A Fine Slice Of SQL Server
Andy Levy takes us through the Get-History cmdlet:
I’m sure that a lot of folks do work on the PowerShell command line, trying various things, before committing to writing a full script or function. Work it out step by step, try a few different things, etc. But once you’ve finished, how can you turn it into a script? Let’s take a look.
Read on for that look.
Comments closedAaron 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
$procedurevariable.
Read on to see what’s new in the ParamParser repo.
Comments closedPatrick Gruenauer contrasts three ways of halting execution in Powershell:
The break statement provides a way to exit the current control block (loop!), Technical reference:
Click through for examples of each operator.
Comments closedJeffrey Hicks takes us through formatting in Powershell and uses Get-Process as an example:
One of the features I truly enjoy about PowerShell, is the ability to have it present information that I need in a form that I want. Here’s a good example. Running Get-Process is simple enough and the output is pretty complete. But one thing that would make it better for me, is that sometimes I want an easy way to see high-memory use properties. Yes, I can pipe Get-Process to Sort-Object and Where-Object. However, in this particular situation, what I really want is to see high-memory usage processes displayed in red. Maybe those that are getting close to my arbitrary limit I’d like to see in Yellow. This isn’t that difficult to achieve using ANSI escape sequences.
Click through to see how.
Comments closedSander Stad walks us through some changes to the data masking algorithm in dbatools:
If you’ve used the data masking command in dbatools you’ve probably noticed that the PowerShell session becomes memory intensive when it has to handle larger tables with one or more unique indexes.
The reason that happens is that during the data masking process the command looks for any unique indexes in the table. If it finds a unique index it will create a unique row for all the columns in the unique index.
Read on to see how Sander handled this.
Comments closedMartin Schoombee gets off the beaten path:
We’ve only used the PowerShell cmdlets for Power BI so far in this series, but things are about to get interesting because there aren’t cmdlets available for everything you might want to do. One such thing is updating parameters, and we’re going to use the Power BI REST API (which the cmdlets use underneath the covers anyways) to achieve that.
REST APIs are usually a little tricky to deal with, especially the process of authentication. Fortunately there is an Invoke-PowerBIRestMethod cmdlet that makes it possible to use the API in PowerShell without the need to deal with some of the underlying complexities.
Read on to see how the whole process works.
Comments closedShabnam Watson has a helpful script for us:
You can use PowerShell to download all of your PBI reports in a workspace all at once without having to go through the PBI service UI one at a time. As an added bonus, you may notice that downloading a report with PowerShell is faster that downloading it through the PBI Service UI.
This script is useful for admins to take backups of reports deployed to PBI Service. It can be easily extended to loop through all/several workspaces. It is also useful for developers to take a backup of their report before publishing a new version.
Click through for the script.
2 CommentsJana Sattainathan takes us through a couple of DBA scenarios:
One of the more frequent requests that a SQL Server DBA receives is to “Clone a login” with all its permissions. For example a request could be
– Clone BILL_BLACK’s login and create a new login JACK_JOHNSON with exactly the same privileges in all databases.
– Clone AD group login BILLING_APP_ADMINS and create a login for new AD group BILLING_APP_CONSULTANTS with the same permissions as BILLING_APP_ADMINS in all databases
Read on to see how.
Comments closedMartin Schoombee continues a series on automating Power BI deployments:
There are two things to keep in mind when creating workspaces in an automated fashion:
– The workspace may already exist. When you delete a workspace, it isn’t really deleted…only its status changes to “deleted”. That’s great if you want to restore a workspace you’ve accidentally deleted, but your code will have to account for it or it will fail.
– Whether you are creating or restoring a workspace, you have to provide an account to be the new administrator in the workspace. This ensures that the workspace is accessible, and by design a workspace cannot exist without an administrator.
Read on for the code.
Comments closedMatthew Roche points out an API update:
Back in August I highlighted the new dataflows PowerShell script repo on GitHub. These scripts provide an accelerated starting point to working with the dataflows REST APIs and to automate common dataflows tasks.
This week the dataflows team has released two new REST APIs for managing dataflows transactions (think “refresh”) and a new parameterized PowerShell script to make using the APIs easier.
Read on to see how you can use these new APIs to trigger a dataset refresh once a dataflow refresh has completed.
Comments closed