Dan's Blog

I am Principal Program Manager at Microsoft leading the Business Platform Division's (BPD) community team. BPD includes SQL Server, SQL Azure, BizTalk, AppFabric, and other technologies and services.

Loading SQL Server Snapins into your PowerShell Session

Loading SQL Server Snapins into your PowerShell Session

  • Comments 4

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"
}

Leave a Comment
  • Please add 1 and 6 and type the answer here:
  • Post
  • Just to be funny - you can use just this:

    asnp SqlServer* -ea 0

  • 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}

  • Hi Dan,

    thanks for this. I noticed though you are loading in the 'SqlServerProviderSnapin110' modules. Are these Denali?

    Thanks

    Paul

  • What is SqlServerProviderSnapin110 and how do I obtain it?

Page 1 of 1 (4 items)