Exec sproc by Powershell
I would like to execute a stored procedure from Powershell (v2) on a SQL Server 2008 database. Based on using C # as my primary language, I do it this way. For example, when I need to run a sproc that returns no results, this is what I do 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 TMTOWTDI is possible, I would like to know a better way.
I must mention that I am already familiar with the T-SQL and System.Data namespace. This is really a Powershell question.
source to share
For direct PowerShell, I would go with the code as you and Andomar wrote. However, if you are using PowerShell Community Extensions , there are some cmdlets for working with ADO, for example:
$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
...
source to share
I have Windows Server 2008 with PowerShell 2.0 and SQL Server 2008 and I was able to use Invoke-SqlCmd to execute sql against the database.
You will need to add snapins using these two commands:
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
After that Invoke-sqlcms will be available for your PowerShell session, see http://technet.microsoft.com/en-us/library/cc281720.aspx for examples of using the -sqlcmd call
ExecuteNonQuery () runs the stored procedure, but does not query for results. You will need to use ExecuteReader () for a set of rows or ExecuteScalar () for a single row with a single value.
Here's an example from a good 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]
source to share