Adding the SQL Server Snap-ins to Windows PowerShell
The sqlps utility is a Windows PowerShell mini-shell. Mini-shells have certain restrictions. For example, they are coded to load in one or more Windows PowerShell snap-ins, but users and scripts cannot add other snap-ins. If you require functionality not supported by a mini-shell, such as working with both the SQL Server snap-ins and the snap-ins from another product, you can add the SQL Server snap-ins directly into a Windows PowerShell environment.
Paste the following code into Notepad and save it as a ps1 script file on your computer, such as C:\MyFolder\InitializeSQLProvider.ps1:
#
# Add the SQL Server provider.
#
$ErrorActionPreference = "Stop"
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
throw "SQL Server Provider is not installed."
}
else
{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}
#
# Set mandatory variables for the SQL Server rovider
#
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-LocationYou can then use the script to launch the Windows PowerShell environment in which the SQL Server snap-ins are loaded by using this command:
PowerShell -NoExit -Command "C:\MyFolder\InitializeSQLProvider.ps1"
The command can be run at a command prompt, from a desktop shortcut, or from the Run dialog box in the Start menu. By default, Windows PowerShell runs in Restricted mode, which does not support running scripts. For more information about enabling Windows PowerShell scripts, see Running Windows PowerShell Scripts.