Capture SQL Exception output from stored procedure

Running against SQL Server 2008 R2.

I am attempting to capture the SQL exception output by executing a stored procedure which forces a divide by zero error ("select 1/0 as booboo").

I am using a SQLCommand object to execute the stored procedure. No problem there.

I've defined a SqlInfoMessageEventHandler to simply write to a text file as follows:

$errout = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Add-Content -Path $outfile -Value "$($_)" }

I've added the handler to the SQlConnection object as:

$SqlConnection.add_InfoMessage($errout)

As documented, the SqlInfoMessageEventHandler outputs PRINT output (Severity <= 10) just fine but I've been unable to determine how to access the output from a Severity 16 (generated by the div/0 error) error.

I saw elsewhere that I should be able to iterate through an $err object in the catch clause but have had no success there, besides I did not see where the $err object would capture the output anyway...

Anyone have any guidance on this?

TIA

René Nielsen over 4 years ago

Update: Not solved, just additional diagnostic information. When I replace my CommandText property of my SqlCommand object ($SqlCmd.CommandText) with an explicit query string ("select 1/0 as booboo") and execute I receive the desired exception output via the SqlConnection.SqlInfoMessageHandler. It displays "Divide by zero error encountered." as it should. The question now is, since that was the only change, why don't I get this when executing with CommandText is a call to a stored procedure with that query string in it?
Cancel
Up 0 Down

Reply

Cancel
Chrissy LeMaire over 4 years ago

Hey Rene,

This is strange, indeed. I was able to execute the following code successfully against SQL Server 2008 R2 on Win10 (System.Data v4.0.30319/PS v5), Win7 (v4.0.30319/PS v3) and WinXP (v2.0.50727/PS v2), but not WinXP (v2.0.50727/PS v1).

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=sqlserver;Database=test;user ID=test;password=test"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = "divbyzero"
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };
$SqlConnection.add_InfoMessage($handler);
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()
$SqlCmd.Parameters.value
$SqlConnection.Close()

The box that didn't work was the SQL Server 2008 R2 server itself. Have you tried connecting from other machines? What is your OS, .NET version and PowerShell version?
Cancel
Up 0 Down

Reply

Cancel