Howdy.

Back in the July Update of SSMS 2016, a bunch of new SQL PowerShell functions were added, plus two neat additions to Invoke-Sqlcmd: -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read.

However, this update has two issues: firstly, it’s not updating the classic sqlps module, but rather has created a new module: sqlserver. This new module will be regularly updated and delivered with SSMS updates. And the sqlps will only be affected by changes from the DB Engine; so Cumulative Updates, Service Packs etc. So there’s quite a bit of overlap here.

Secondly, I don’t like how this new module is being delivered via a monthly release of SSMS. That’s a 900MB download and a long install to gain access to a module that is only 2MB in size. To make matters worse, the SQL Server 2016 Feature Pack, which includes a SQL PowerShell msi, IS NOT being updated! Waah!

So this may just seem like a whinge, but I have checked and it is possible to install this module on it’s own and use without having anything else other than a couple of prerequisites installed. So I’m going to be pro-active: I have taken in upon myself to upload the module to NuGet for people to consume. As far as prerequisites go, you only need SMO and SQLCLR installed, available through the Feature Packs, though they do have to be the SQL Server 2016 versions of these objects.

As mentioned in one of the links above, you have to Remove-Module sqlps first before you import the new module.

I’ll update the NuGet package every time there’s a new release of SSMS.

Oh, and here’s a quick syntax of how to import the module:

Import-Module -Name "C:\nuget\PowerShell.SqlServer.Modules.1.0.1\sqlserver" -Verbose
$conn = ".;Integrated Security=True"
Invoke-Sqlcmd -ConnectionString $conn -query "SELECT GETDATE() AS TimeOfQuery;"