Problems using SQL Server 2008 R2 PowerShell extensions outside of SQLPS

Question

I would like to have the SQL Server PowerShell extensions available to me whenever I start PowerShell by loading the snap-ins in my profile.ps1 script. I found an article here with a script example that shows how to do this, and this works fine on my 32-bit Windows XP box.

Unfortunately, on my 64-bit Windows 7 machine, this blows up. If I try to launch this script with the 64-bit PowerShell, I get:

Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.
At C:\Users\xxxx\Documents\WindowsPowerShell\profile.ps1:84 char:13
+ Add-PSSnapin <<<<  SqlServerCmdletSnapin100
+ CategoryInfo          : InvalidArgument: (SqlServerCmdletSnapin100:String
[Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

If I run this instead in a 32-bit PowerShell, I get:

Get-ItemProperty : Cannot find path 'HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds \Microsoft.SqlServer.Management.PowerShell.sqlps' because it does not exist.
At C:\Users\xxxx\Documents\WindowsPowerShell\profile.ps1:39 char:29
+     $item = Get-ItemProperty <<<<  $sqlpsreg
+ CategoryInfo          : ObjectNotFound: (HKLM:\SOFTWARE\...owerShell.sqlps:String) [Get-ItemProperty], ItemNotFoundException
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemPropertyCommand

I'd like to be able to run this in a 64-bit PowerShell if possible. To this end, I tracked down what I thought was the Powershell extension dlls and in a 64-bit Administrator elevated PowerShell I ran:

cd "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn"
installutil Microsoft.SqlServer.Management.PSProvider.dll
installutil Microsoft.SqlServer.Management.PSSnapins.dll

No dice. Although installutil seemed to indicate success, I still get the "No snap-ins have been registered for Windows PowerShell version 2" error message when I run the script.

Anyone have any suggestions as to where I go from here?

Answer 1

I've used this script without issue on x64 machines. The problem with the x86 invocation is that the script looks for registry keys which on an x64 instance are only accessible from x64 PowerShell. For the x64 invocation you could try registering the snapins since that is the error message you're receiving. Run as administrator...

Change this:

cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100 

to this:

cd $sqlpsPath
$framework=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())
Set-Alias installutil "$($framework)installutil.exe"
installutil Microsoft.SqlServer.Management.PSSnapins.dll
installutil Microsoft.SqlServer.Management.PSProvider.dll
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100    

An even better solution is not use add-pssnapin instead turn sqlps into a module. I have blog post here: http://sev17.com/2010/07/10/making-a-sqlps-module

Update for SQL Server 2012 - now ships a sqlps module you can install instead of the above blog: http://www.microsoft.com/en-us/download/details.aspx?id=35580