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:

IsLifeGood_Cortana

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

LifeIsGood_Cortana

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…

pserrors_2012_win10

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:

versiontable_Win10_2012R2

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.

LoadedAssemblies_Win10_2012R2
Loaded assemblies between the two servers. Click for a larger version.

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"

Win10_SQLPSFullPathTo2014

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

Win10_2014_RightModuleGoodResult

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:Win10_PSModulePath
    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.

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

    1. Drew Furgiuele

      No problem, happy to help. I wish I could find a real reason for the issue though. Thinking of opening a Connect bug for it, but it could very well be the way I coded it, too.

  1. Cody

    The WMI error is normal when loading the SQL 2012 provider and accessing a SQL 2014 instance they hardcode the WMI namespace so it’s not forward compatible. Hilariously the SQL 2014 provider ended up with a bug where they accidentally overwrote the SQL 2012 namespace instead of adding successive tests (like they did with 2005 and 2008). There’s a Connect bug for that.

    As to why it’s loading differently versioned assemblies only on Windows 10… I’m not sure 🙂

    1. Drew Furgiuele

      The WMI warnings I can live with; I’m used to those. Plus if you install the Azure PowerShell modules on your system you get a lot more of them when you attempt to do anything, too. The crux of the errors I was having was the libraries for SQL 2012’s module don’t seem to “work” with PowerShell 5.0.

  2. Josh Simar

    Could it be the order that they were placed in the path variable? Had it hit the 120 path first it would have loaded those first and not had issue?

    In that case could it have been the order that you installed 2012 and 2014 on your system?

    1. Drew Furgiuele

      Yes, it was exactly due to the order. Installing 2012 FIRST put that path ahead of the 2014 path in the module path string, so that’s why it looked in that directory first. The fix makes it work, but I’m more concerned about people who don’t install management tools for 2014 on their workstations. It appears that the 2012 tools just don’t work correctly with Windows 10 and PowerShell 5.0.

Comments are closed.