Get SQL print message into a variable with ExecuteNonQuery() in Powershell

Question

I have this simple Powershell script that outputs the message "Hello World!" from SQL PRINT command:

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server>;Database=msdb;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true;
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT 'Hello World!'";
$res = $cmd.ExecuteNonQuery();
$conn.Close();

I need to catch and parse this print output. How can I put it into a variable (in addition to just printing it out)?

Reg Edit · Accepted Answer · 2022-03-05 19:07:50Z

5

You should assign the value of $event.Message to the global variable $global:msg (or other name starting with $global:). Then this variable will be reachable outside of the event handler scope:

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server name>;Database=msdb;Integrated Security=SSPI;";
$global:msg = ""
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
    param($sender, $event) 
    $global:msg = $event.Message
    Write-Host $global:msg
};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT 'Hello World!'";
$res = $cmd.ExecuteNonQuery();
$conn.Close();
Write-Host "The event handler message '$global:msg' is inside of the global variable!"

edited Mar 5, 2022 at 19:07

Reg Edit

6,9642 gold badges42 silver badges48 bronze badges

answered Apr 3, 2020 at 10:10

Dmytro Sly

3134 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

strange walker · Accepted Answer · 2020-04-02 16:37:57Z

-1

I suggest reversing order: putting in a variable first, then printing it out:

$message = 'Hello World!'

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=<server>;Database=msdb;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message};
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true;
$conn.Open();
$cmd = $conn.CreateCommand();
$cmd.CommandTimeout=0
$cmd.CommandText = "PRINT $message";
$res = $cmd.ExecuteNonQuery();
$conn.Close();

answered Apr 2, 2020 at 16:37

strange walker

991 bronze badge

1 Comment

Dmytro Sly Over a year ago

This will not work as I don't know what text of the output will be.

Collectives™ on Stack Overflow

Get SQL print message into a variable with ExecuteNonQuery() in Powershell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related