Exec sproc from Powershell

Question

I would like to execute a stored procedure from Powershell (v2) against a SQL Server 2008 database. Coming from using C# as my primary language, I'm doing it in that fashion. For example, when I need to run a sproc that doesn't return results, here is what I'm doing now:

$con = new-object System.Data.SqlClient.SqlConnection($connectionString)
$cmd = new-object System.Data.SqlClient.SqlCommand("exec MySproc", $con)
$con.Open()
$cmd.ExecuteNonQuery()
$cn.Close()

While perhaps TMTOWTDI, I'd like to know the best way.

I should mention that I'm already familiar with T-SQL and the System.Data namespace. This is really a question about Powershell.

Answer 1

ExecuteNonQuery() runs a stored procedure but does not ask for results. You'd have to use ExecuteReader() for a rowset, or ExecuteScalar() for one row with one value.

Here's an example from this nice tutorial:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "..."
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "sp_helpdb"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Answer 2

For straight PowerShell I would go with code like you and Andomar have written. However if you are using the PowerShell Community Extensions there are some cmdlets for working with ADO e.g.:

$conn = 'Data Source=.\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI'
$ds = Invoke-AdoCommand -ProviderName SqlClient -ConnectionString $conn `
          -CommandText 'Select * from Authors' -AsDataSet
$ds.Tables


au_id    : 172-32-1176
au_lname : White
au_fname : Johnson
phone    : 408 496-7223
address  : 10932 Bigge Rd.
city     : Menlo Park
state    : CA
zip      : 94025
contract : True

...

Answer 3

Hi, I have a Windows Server 2008 with PowerShell 2.0 and SQL Server 2008 and I have been able to use the Invoke-SqlCmd to execute sql against a database.

You'll need to add the snapins with these two commands:

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

After doing this the Invoke-sqlcms will be avaliable to your PowerShell session take a look at http://technet.microsoft.com/en-us/library/cc281720.aspx for examples of using the invoke-sqlcmd