By Richie Lee

Hello!

I’ve been working on some pre-deployment scripts to SQL Server using PowerShell, and I chose to use Invoke-Sqlcmd as opposed to creating a sql connection*, however the first time I ran the script on my local box to test, it seemed to hang. I thought it may be my local instance not running, but that was running fine. So I created the simplest command possible and tried it:


Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "."

But still it was hanging. And then warning messages started to appear:

slowtoimport_png

And at the end of the messages, my query returned. Subsequent re-runs of the Invoke-Sqlcmd ran almost instantly. But the errors pertain to Azure, and I’m not using Azure!. But I do have the Azure modules installed on my machine. A little bit of Googling led me to this Connect issue. Essentially what is happening is tha the Import-Module that is ran implicitly when using Invoke-sqlcmd “incorrectly loads files (e.g. DLLs, .ps1xml, etc) in the context of the current provider, as opposed to the context of the “file system” provider (this causes the SQL Provider to try to connect to entities that may look like SQL Server, but they are not - as stated in the description of this issue; there isn’t anything intrinsically wrong that the SQL Provider is doing here.)"

And now for the good news: it’s fixed. Hurrah! But now for the bad news: you need to install SQL Server 2016 to get the fix. I did install just the June 2016 release of SSMS as it’s far more realistic for people to upgrade this than the database engine, but unfortunately this did not resolve the issue even after a restart. It was a long shot, but one worth trying. You can also uninstall the Azure modules; it’s arguably easier to uninstall these and run them on another box than upgrade to SQL Server 2016, but I’ll admit that’s hardly a great solution.

*I have found that it’s easier to set Invoke-sqlcmd to verbose and save any print messages from SQL to a file that can then be saved as an artifact to be used for deployment than any other way.