3 Things To Do If Invoke-SqlCmd Is Not Recognized in Windows PowerShell

UPDATE: The notes below were written for SQL Server 2008. As of 2012, Microsoft has switched from 2 snapins to 1 module. See below for details. Thanks to iainelder in the comments below.


I had a client request a data export from their web application to be FTP'd to a server of theirs on a daily basis. So, I quickly set about writing a sqlcmd script to pull out the data that I needed and save it to a file. It wasn't long before I wanted the deliciousness that Windows PowerShell provides, and a quick search showed me 'Invoke-Sqlcmd'. When I tried to run it, I got this error:

The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program.

The source script that I was using to guide me assumed that invoke client was available when it wasn't. After a lot of reading and trial and error, I was finally able to get it to run on my machine, and then also on the server.

Since all of the steps I required weren't listed in one place, I thought I'd list them here in case it helps anyone:

  1. Install SQL Server 2008 R2 Management Objects using Web PI (I'm not sure about versions prior to 2008 R2... if you have more info, please let us know in the comments)
  2. Install 'Windows PowerShell Extensions for SQL Server' from the Microsoft® SQL Server® 2008 R2 Feature Pack page (it's about halfway down the page). Make sure you pick the correct flavor for your instance (32 or 64 bit).
  3. Run these two commands before calling invoke-sqlcmd in your script:
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100

UPDATE: for SQL Server 2012, they have switched to a single module. Run this instead:

Import-Module SqlPs

Hopefully this helps someone!