THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

SQL Server PowerShell Provider follows the Version of PowerShell on the Host and other errata

There may be some misunderstanding on how the PowerShell Provider for SQL Server works. I’ve written an article or two explaining that you can use PowerShell with SQL Server, without having the SQL Server 2008 (or higher) provider around. After all, PowerShell just uses .NET, and SQL Server “Server Management Objects” or SMO listen to that interface as well.

In SQL Server 2008 and higher we created a “MiniShell” for PowerShell that gives you the ability to treat a SQL Server Instance as a drive (called a “Provider” or path or drive) and a few commands (called command-lets). Using these two simple constructs you can move around SQL Server quickly and work with the objects it holds.

I read the other day where someone stated that we had “re-compiled” PowerShell, so that you would have version 1.0 from SQL Server and 2.0 on your new server. Not so! Drop to a SQLPS prompt and a PowerShell prompt and type this in each:


They should return the same value.

You can think of a MiniShell as simply a compiled “profile” that gives you those providers and command-lets automatically – that’s all. In fact, you can load the SMO libraries yourself without the SQL Server 2008 Provider anywhere in sight. I do this all the time, since the MiniShell also has other restrictions.

Also remember that if you run a PowerShell script as a SQL Agent Job step type (in 2008 and higher) that you’re running under the context of the account that starts Agent – I think most folks know this, but it’s good to keep in mind.

There’s a re-written section of Books Online that goes over working with this very nicely – also covers the question “How to I connect to another server using the SQL Server PowerShell Provider” (hint: It’s just CD) and “How do I load all the SMO stuff if I don’t want to use the Provider” and more. Be sure and check out the note at the bottom that explains the firewall exceptions you’ll need to enable to CD to that remote server. Here’s that link:

Published Wednesday, May 5, 2010 8:01 AM by BuckWoody
Filed under: ,



Chad Miller said:

Although, technically, sqlps neither PowerShell V1 nor V2, the answer to this question is a little more complicated and a closer look into the inner workings of sqlps is needed. sqlps was created via the Windows SDK utility make-shell. When you create a minishell you reference the PowerShell and SQL Server PowerShell assemblies. The utility creates a RunspaceConfiguration which explicitly defines what cmdlets are available in the minishell. The minishell was created referencing PowerShell V1 assembly, which means only V1 plus SQL Server cmdlets are available in sqlps when running on V2.

Now, this doesn't mean sqlps is V1. So, sqlps is not a a re-compiled PowerShell, but it is a compiled PowerShell host creaed using make-shell that implements a constrained RunspaceConfiguration.

See a more detailed explanation here:

May 28, 2010 7:46 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement