Upgrading to Windows 10 on your workstation? Write PowerShell that uses SQLPS? About that…

Ooh, shiny!

Unless you don’t keep up with these things, Microsoft released Windows 10 not too long ago. I’m a sucker for the new hotness, so I finished up a couple projects last week, and early this week I backed-up what I would need and bit the bullet of a so fresh and so clean install of the new operating system. I had been using the OS at home for a few weeks prior thanks to my MSDN license, but actually using the machine to earn a living and not just blogging and playing World of Warships is something different entirely.

After fumbling about a bit with Bitlocker, a Surface 3 Pro that was several firmware updates behind, and a busted USB stick, I finally got the OS installed and set about getting everything set up. Since this is my local workstation I went with Windows 10 Enterprise, and in my production environment at work we have several different versions of SQL Server so I installed SQL Server 2012, SQL Server 2014, Visual Studio 2012 and 2013 (with SQL Server Data Tools so I can troubleshoot the myriad of SSIS packages if they blow up), and a handful of other required applications. Life was good. I know this because I asked Cortana if it was:

To which, she… opened a web browser? To… have me buy some clothes?

I see you, Cortana.

Marketing ploy aside, I took it for what it was: Life was now good, even if I didn’t have some sweet pajama pants that exclaimed it. It was time to get to work.

$PSVersionTable and Loaded Assemblies

And I didn’t have to wait long: someone needed help moving some data around. Fortunately for me, I have a script for that. I fired up a PowerShell terminal and… oh yeah! PowerShell version 5.0! Bitchin’. Let me go ahead run this and I’ll play with it when I’m done… I wonder if…

Whoa, what? That’s… not supposed to happen. This just worked yesterday? I mean, sure, I have a new OS, but this is using the SQLPS module, and shouldn’t care so much about PowerShell 5.0, right? What the heck; I had a super similar configuration, sans Windows 10, on my old machine. What the heck happened?

Clearly the only thing that changed was my OS, so let’s do a quick compare of the PowerShell versions on my new machine vs. a staging server running Server 2012R2 with SQL Server 2014. We can do that with the handy built-in variable $PSVersionTable:

On the left we can see PowerShell 5.0 is the PSVersion, and on the right is 4.0. Okay… but so what? My script works fine on the 2012R2 Server… so clearly something is different? But what? Well, just for fun lets run a command that looks goofy, but at least it will tell us if we’re loading the same assemblies in both shells on different machines:

[appdomain]::currentdomain.getassemblies()

Running this command gives us a listing of every loaded assembly for the session, and whether it’s in the GAC or not. The short version is: when you use the command Import-Module SQLPS in PowerShell, you’re really executing a predefined set of scripts that load assemblies. And then… I noticed something odd.

On the left, it looks like it loaded SQL Server related assemblies from the 110 directory on my local machine (which is SQL Server 2012) and on the right, where we only have SQL Server 2014, it loaded from the 120 directory. Okay, well then: what if I load the SQLPS module by specifying a path to the actual module?

import-module "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS"

And now what if I try to run my script again?

Bingo! Everything looks like it works now.

So what happened?

In this particular case, the root cause appears to be compatibility between PowerShell 5.0 and the 110 version of the SQLPS module for SQL 2012. But, as we see, the 2014 version works fine. So how do we work around this? You have three options:

1. Manually load any assemblies you need with Add-Type. This can be painstaking but you can at least specify which assembly you need to load either by giving the direct path to the libraries. This is fine for local development, but if you plan on distributing code, you now need to account for versions other than 120. Tough, but doable.
2. Manually load the version of the module you plan on working with. This is preferable if you have legacy code that works with an older version of SQLPS, but you need to develop locally and you have multiple versions of SQL Server installed locally.
3. Modify the $PSModulePath environment variable. The root cause of all this trouble is the environment variable SQL Server setup creates. When we open the Environment Variables tab in the advanced system properties, we see this:
   And if you copy and paste that string out, it looks like this

   %SystemRoot%\system32\WindowsPowerShell\v1.0\Modules\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\;C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\

   So you can see that there are two paths, one for the 2012 install we did, and then the 2014 install. If we remove the path to the 2012 install folder, any time we run import-module sqlps it will only be looking in the 2014 install folder. This is how I got around it. After a reboot, everything was happy again.

So there you have it. If you just upgraded and scripts are breaking, this may be why. Of course, it all depends on your implementation and code.