No snap-ins have been registered for Windows PowerShell version 2

Question

I am trying to run a Powershell script on a web server where SQL Server Management Studio is not installed but all pertinent packages from the Microsoft SQL Server 2008 R2 SP2 Feature Pack have been installed. You need to install those small bits and pieces in order for Powershell to be able to run SQL commands.

Then I ran this setup script that preps your environment for SQL Server commands run with Powershell:

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Powershell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}



/* Preload the assemblies. Note that most assemblies will be loaded when the provider
 is used. if you work only within the provider this may not be needed. It will reduce
 the shell's footprint if you leave these out.*/

$assemblylist =
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum"


foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}


//Set variables that the provider expects (mandatory for the SQL provider)

Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000


//Load the snapins, type data, format data

Push-Location
cd $sqlpsPath


Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100 
Update-TypeData -PrependPath SQLProvider.Types.ps1xml  
update-FormatData -prependpath SQLProvider.Format.ps1xml  
Pop-Location

At Add-PSSnapin SqlServerCmdletSnapin100, the script fails with the following error:

No snap-ins have been registered for Windows PowerShell version 2. At C:\Vantiv\Initialize-SqlpsEnvironment.ps1:75 char:13 + Add-PSSnapin <<<< SqlServerCmdletSnapin100 #-ErrorAction SilentlyContinue + CategoryInfo : InvalidArgument: (SqlServerCmdletSnapin100:String) [Add-PSSnapin], PSArgumentException + FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

I ran a Google search on this error and the only solution I could find was that people were saying my 64-bit Powershell Console shortcut was pointed to the SysWOW64 directory rather than the System32. This is not the case for me. Mine points to System32.

Any ideas? What do I need to do to get Powershell to register those snap-ins?

Answer 1

If you do:

Get-PSSnapin -Registered

you'll get a list of ready-to-use snap-ins for PowerShell (here just those for SQL) :

Name        : SqlServerCmdletSnapin100
PSVersion   : 2.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin100
PSVersion   : 2.0
Description : SQL Server Provider

if you can't see these snap-ins in the list, try the solution posted here.