14

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?

Irinotecan
  • 567
  • 1
  • 6
  • 12

3 Answers3

20

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

adrianbanks
  • 81,306
  • 22
  • 176
  • 206
Chad Miller
  • 40,127
  • 3
  • 30
  • 34
  • Since I didn't want to have to use an administration elevated PowerShell every time I added the snap-ins, I used your SQLPS module method from your blog, and it works like a charm! Thank you! – Irinotecan Jan 28 '11 at 19:16
  • No problem. I too prefer the module method, but keep in mind registration for a new snapin only need be run once as an administrator. After that you can just run add-pssnapin. – Chad Miller Jan 29 '11 at 03:22
  • Same problem with SharePoint PS snapin. The solution works too in my case (just have to lcate the DLL in the GAC: `C:\Windows\assembly\GAC_MSIL\Microsoft.SharePoint.PowerShell\14.0.0.0__71e9bce111e9429c\Microsoft.SharePoint.PowerShell.dll`). Thanks – Steve B Sep 12 '13 at 20:14
  • @ChadMiller Which do we have to download in your last link? I can't see anything titled `sqlps`! – J86 Mar 05 '15 at 13:57
  • Regarding "Update for SQL Server 2012 - now ships a sqlps module" -- does this mean I can install a (free) SQL Server 2012+ ("Express" is free?) in order to get the Powershell Snap-Ins? – Nate Anderson Jul 22 '15 at 18:26
2

I realise this is a bit of an older question but with a stock standard Windows and SQL Server 2012 install you can just directly use the command Invoke-Sqlcmd without loading anything beforehand as it will auto import the sqlps module. However letting it do that will often cause issues so import the module yourself with the lines below in the same place in your code as you used to use the add-pssnapin commands

$cur = Get-Location
Import-Module 'sqlps' –DisableNameChecking
Set-Location $cur

Similar to that posted on this MS web forum.

The import-module line above changes the current path to something that makes UNC path strings like "\\server\share\path\filename.ext" not work with lots of cmd-lets. So we store the current path before and change it back after the import-module command.

BeowulfNode42
  • 518
  • 7
  • 21
  • or `Push-Location; Import-Module 'sqlps' –DisableNameChecking; Pop-Location` – Chase Florell Jan 12 '15 at 22:24
  • @ChaseFlorell: I tried push and pop location, but kept getting an error message saying that Pop-Location didn't exist (after the import-module was run). Using a variable as suggested by BeowulfNode42 seems much more reliable. – jmoreno Mar 16 '15 at 23:56
0

It's possible the snapin assemblies are compiled for x86 only due to dependencies on native 32bit SMO COM objects. If it was possible to run them in a 64bit shell, I'm pretty sure MS would have shipped both x86 and x64 management shells.

x0n
  • 51,312
  • 7
  • 89
  • 111