PowerShell 64 bit and Sql Cmdlets

I was getting the following error when I tried to execute a script that tried to load the Sql Powershel cmdlets.

Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.

This error occurred only 64 bit machines and only when I ran the 64 bit version of Powershell.

To get around it – I had to go through the following 2 steps:

Step 1: update the registry to insert an entry in the Wow6432Node hive.
After installing Sql Management Studio on the machine where you are seeing this error, run the following script to setup the registry correctly.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps]
"Path"="C:\\Program Files (x86)\\Microsoft SQL Server\\100\\Tools\\Binn\\SQLPS.exe"
"ExecutionPolicy"="RemoteSigned"

Step 2: Install and add the powershell cmdlets.

Next run the following Powershell script to install the Sql powershell cmdlets.

#
# Add the SQL Server Provider.
#
if ($SqlServerMaximumChildItems -ne $Null -and $SqlServerMaximumChildItems -ge 0)
{
    write-host '$SqlServerMaximumChildItems is defined - sql provider is already loaded. Exiting load routines....'
    exit
}
else
{
    write-host '$SqlServerMaximumChildItems is not defined - continuing to check if sql provider needs to be loaded'
}
$ErrorActionPreference = "Stop"
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}
#
# Set mandatory variables for the SQL Server 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

Push-Location
cd $sqlpsPath
$framework=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())
Set-Alias installutil "$($framework)installutil.exe"
#following needs to be done only once on any machine – the script must be run
#in a Powershell window that has been spawned with admin priviledges
installutil Microsoft.SqlServer.Management.PSSnapins.dll
installutil Microsoft.SqlServer.Management.PSProvider.dll

# Load SqlServerProviderSnapin100
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin100'}))
{
    if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerProviderSnapin100'})
    {
       Add-PSSnapin SqlServerProviderSnapin100
       write-host "Loading SqlServerProviderSnapin100 in session"
    }
    else
    {
       write-host "SqlServerProviderSnapin100 is not registered with the system." -Backgroundcolor Red –Foregroundcolor White
       break
    }
}
else
{
  write-host "SqlServerProviderSnapin100 is already loaded"
} 

# Load SqlServerCmdletSnapin100
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'}))
{
    if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerCmdletSnapin100'})
    {
       Add-PSSnapin SqlServerCmdletSnapin100
       write-host "Loading SqlServerCmdletSnapin100 in session"
    }
    else
    {
       write-host "SqlServerCmdletSnapin100 is not registered with the system."
       break
    }
}
else
{
  write-host "SqlServerCmdletSnapin100 is already loaded"
}

Pop-Location
write-host 'initializeSqlServerProvider.Ps1 has completed'