Loading SQL Server Snapins into your PowerShell Session

  • Article

Caveat: I don’t write code for a living. But I do know how to get things done, usually using brute force.

SQLPS.exe is a decent environment, but sometimes I want to work in the default PowerShell environment. But if I want to work with SQL Server in the default PowerShell shell it means I need to load the SQL Server snapins into my session. Just because I’m at MS doesn’t mean I intuitively know all of the answers, though I can usually find someone who does. Sometimes, though I like to try and figure it out on my own. To feel the pain of a real user.

I’ll cut to the chase. There are probably many blog postings and articles on this already but getting a few more to pop-up in the search results doesn’t hurt. So here it goes. There are two SQL Server Snapins you need to load into your PowerShell session: SQLServerProviderSnapin100 and SQLServerCmdletSnapin100. These ship with SQL Server 2008 and SQL Server 2008 R2.

The Provider snapin is explained here. The Cmdlet snapin is explained here. Now depending upon what you’re doing in your script you may need to load one, the other, or both. I generally just load both so I don’t surprise myself when I attempt to do something and it fails. You can also add the loading to your PowerShell profile or keep it in each of your scripts. I personally like to keep it in my scripts so that when I share scripts with other people (or move them to another machine) everything just works. In other words it makes the scripts more portable.

Enough talk, here’s what you add to your scripts. I’m expecting feedback on how to simplify the logic!

# Load SqlServerProviderSnapin100
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin110'}))
{
if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerProviderSnapin110'})
{
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"
}

Comments

  • Anonymous
    March 31, 2011
    Just to be funny - you can use just this: asnp SqlServer* -ea 0

  • Anonymous
    April 05, 2011
    David, I really like the simplicity of your method.  Here is how I have mine set up in my profile if (!(Get-PSSnapin -Name SQLServerCmdletSnapin100 -ErrorAction SilentlyContinue)) {    Add-PSSnapin SQLServerCmdletSnapin100} if (!(Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {    Add-PSSnapin SqlServerProviderSnapin100} if (!(Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {    Add-PSSnapin SqlServerProviderSnapin100}

  • Anonymous
    May 12, 2011
    Hi Dan, thanks for this. I noticed though you are loading in the 'SqlServerProviderSnapin110' modules. Are these Denali? Thanks Paul

  • Anonymous
    March 29, 2013
    What is SqlServerProviderSnapin110 and how do I obtain it?