Execute stored procedure in Powershell and get print statements output on host

I had a requirement to execute the stored procedure from Powershell. I cannot use SQLCMD as it was not installed on the application server. I had to use Microsoft .Net framework library to execute the stored procedure.

The below code snippet was used to execute the stored procedure and it works just fine.

$connectionString = "Data Source=SRV01; Integrated Security=SSPI; Initial Catalog=DB01"
$id = 12345
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$sqlCommand = "EXEC [usp_get_details] $id, 0"
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$connection.Close()

But the PRINT messages from the procedure were not displayed on the screen, which are required to identify the progress or status of the procedure execution. I have searched in internet and found useful post by Jonathan at https://www.sqlskills.com/blogs/jonathan/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell/

After implementing the event handler as described in the above link the code looks like below and works as expected.

$connectionString = "Data Source=SRV01; Integrated Security=SSPI; Initial Catalog=DB01"
$id = 12345
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

## Attach the InfoMessage Event Handler to the connection to write out the messages
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };
$connection.add_InfoMessage($handler);
$connection.FireInfoMessageEventOnUserErrors = $true;

$sqlCommand = "EXEC [usp_get_details] $id, 0"
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$connection.Close()
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s