Azure Automation Runbook: Execute SQL Scripts and Capture Print Statements

In my last post, I wrote about how we can execute SQL Scripts on an Azure SQL Database with Managed Identities using an Azure Automation Runbook. In this post, let's see how we can Capture the Print Statements in the SQL script, so we can output those.

Consider the following Stored Procedure.

CREATE OR ALTER PROC dbo.TestSelectStoredProcedure 
(
    @Param1 INT,
    @Param2 VARCHAR(100) 
)
AS
BEGIN
PRINT CONCAT('Param1: ', @Param1)
PRINT CONCAT('Param2: ', @Param2)
END

I need to execute this script and capture the Print messages, so I can output those. In this post, I am not going to explain how we can execute a SQL script from an Azure Automation Runbook, (for that you can read this post: Azure Automation Runbook: Execute SQL Scripts with Managed Identities).

My Runbook is PowerShell 7.1 (preview). This approach would still work if you are using PowerShell 5.1 Runtime.

Now to capture the messages the SP is printing, we need to register SqlInfoMessageEventHandler to the SqlConnection as follows.

# Reading from variables
$AzureSqlServer = Get-AutomationVariable -Name "AzureSqlServer"
$ApplicationDatabase = Get-AutomationVariable -Name "ApplicationDatabase"
$ClientId = Get-AutomationVariable -Name "AzureSqlManagedIdentityId"
 
# Getting AccessToken for User assigned Managed Identity
# Note: Query Parameter now includes the client_Id
$Resource = "https://database.windows.net/"
$QueryParameter = "?resource=$Resource&client_Id=$ClientId"
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
$Headers.Add("Metadata", "True") 
$Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
	-UseBasicParsing `
	-Uri $Url `
	-Method 'GET' `
	-Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
$AccessToken = $Content.access_token 
 
# PowerShell/ADO.NET
$SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
$SqlConnection.ConnectionString = "Server=$AzureSqlServer;Initial Catalog=$ApplicationDatabase;Connect Timeout=30" 
$SqlConnection.AccessToken = $AccessToken 
$Events = new-object System.Collections.Generic.List[Object]
$Handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { param($sender, $event) $Events.Add($event) }
$SqlConnection.add_InfoMessage($Handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection.Open();
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = "exec dbo.TestStoredProcedure @Param1=100, @Param2='Hello World'"
$SqlCommand.ExecuteNonQuery() | Out-Null
ForEach($event in $Events)
{
    Write-Output $event.Message
}
$SqlConnection.Close();

And now when I test this Runbook, I am seeing the following output as expected.

SQL Script Print Messages Being Outputted

Hope this helps.

Happy Coding.

Regards,
Jaliya