Press "Enter" to skip to content

Category: Powershell

Exporting SQL Server Configurations

Published 2020-11-11 by Kevin Feasel

Garry Bargsley shows how you can export configuration settings for your SQL Server instances:

Have you ever deleted a login by mistake from a hastily typed TSQL script or dropped a list of logins because the “Business” said they are not used anymore? Have you ever made a change to a SQL Server Agent job and then it failed on the next execution. What about that time you changed the Database Mail profile on all of your servers and left your personal account in the script instead of the DBA distribution list.

While each of these examples is not life-threatening, they will strike fear in you depending on how prepared you are to recover the items in question.

This is the type of thing you’d want to store in source control, too. That way, you have a record of changes over time.

Comments closed

Auto-Checking Azure Data Factory Setup

Published 2020-11-09 by Kevin Feasel

Paul Andrew is at it again:

Building on the work done and detailed in my previous blog post (Best Practices for Implementing Azure Data Factory) I was tasked by my delightful boss to turn this content into a simple check list of what/why that others could use…. I slightly reluctantly did so. However, I wanted to do something better than simply transcribe the previous blog post into a check list. I therefore decided to breakout the Shell of Power and attempt to automate said check list.

Sure, a check list could be picked up and used by anyone – with answers manually provided by the person doing the inspection of a given ADF resource. But what if there was a way to have the results given to you a plate and inferring things that aren’t always easy to spot via the Data Factory UI.

Paul uses an ARM template rather than hitting your Data Factory directly, so there’s a little bit more work for you the user, but Paul explains why it’s both necessary and proper.

Comments closed

Triggering a Refresh with Power BI’s API

Published 2020-11-05 by Kevin Feasel

Martin Schoombee continues a series on automating Power BI deployments:

At times you may want to refresh a Power BI dataset from outside the portal, either on-demand or as part of another process (think DevOps for instance). In those cases the API provides the ideal mechanism to do so. Just remember that you are still limited to 8 refreshes a day if you don’t have a Premium workspace, and using this method will not work beyond the number of allowed refreshes. You also cannot count on the API to return a useful error message in that case.

Read on to see how.

Comments closed

PIVOT in Powershell

Published 2020-10-29 by Kevin Feasel

Shane O’Neill succumbs to peer pressure:

I can’t very well give out to people for not doing the right thing first time, even if it’s more difficult, if I don’t do the right thing myself!

As Kevin mentioned, once the data was in a proper format, a format designed for SQL, the calculations were trivial.

However, outputting the results in the same way in PowerShell required a way to pivot results in PowerShell. Thanks to some heavy lifting from Joel Sallow ( Blog | Twitter ), I now know how to pivot in PowerShell!

Here’s hoping that this post will help explain it for you also.

It’s interesting to see how much more difficult it is to turn a “tall” data set into a “long” data set in Powershell. It’s not that many lines of code once you know how to do it, but getting there is a challenge.

Comments closed

Updating a Power BI Refresh Schedule

Published 2020-10-28 by Kevin Feasel

Martin Schoombee continues a series on automating Power BI deployments:

There’s a few things you need to pay close attention to when setting the refresh schedule via the API:

– Unless you’re setting the refresh schedule for a Premium workspace, you can only refresh a dataset up to 8 times a day. We’re only going to set it to update once a day here, but keep this in mind if you’re planning to adjust the API call to refresh multiple times a day.

– The name of the time zone you provide has to match exactly with the names (middle column) in this reference: Microsoft Time Zone Index

– The refresh time has to be in the format hh:mm, and similar to the options in the Power BI portal you can only refresh on the hour or half-hour.

Read on to see how it works and the API call to make.

Comments closed

Finding Row Totals in Powershell

Published 2020-10-23 by Kevin Feasel

Shane O’Neill needs a better data model:

Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.

Click through to see a result in SQL Server and then trying to replicate it in Powershell. It’d be a lot easier, though, with a properly normalized data model which includes date, attempt number, and push-ups in that attempt. Pivot those results at the end if you want this sort of report, but SQL is designed to work best with tables in first normal form or higher.

Comments closed

Parsing Parameter Default Values in Powershell

Published 2020-10-19 by Kevin Feasel

Aaron Bertrand continues a series:

In part 1 and part 2 of this series, I introduced ParamParser: a PowerShell module that helps parse parameter information – including default values – from stored procedures and user-defined functions, because SQL Server isn’t going to do it for us.

In the first few iterations of the code, I simply had a .ps1 file that allowed you to paste one or more module bodies into a hard-coded $procedure variable.

Read on to see what’s new in the ParamParser repo.

Comments closed