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)


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

3 answers

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

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





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




All Articles