Capture SQL Exception output from stored procedure

René Nielsen
over 4 years ago

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

  • 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?
  • 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?