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

Parents
No Data
Reply
  • 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?

     

Children
No Data