0

I'm trying to enhance error handling. MSDN shows an example how to handle a SqlException and the SqlException.Errors Property. I built a stored procedure on SQL Server which generates some output. This procedure is called from a .NET application with MSDN-like error handling.

However the order of the messages is other than in SQL Server Management Studio. How do I get the SqlException.Errors in the same order as Management Studio?

Stored Procedure:

CREATE PROCEDURE [dbo].[___PROVOKE_ERRORS]
AS
BEGIN
    SET NOCOUNT ON;
    SET LANGUAGE ENGLISH;

    declare @tab table(id int, word varchar(10))
    declare @id int;

    set @id = (select top(1) object_id from sys.objects)
    print 'Check-ID #1: ' + cast(@id as varchar(10))

    insert into @tab (id, word) values (0, 'test')
    insert into @tab (id, word) values (1, '1234567890abcdef')

    set @id = (select top(1) object_id from sys.objects order by name)
    print 'Check-ID #2: ' + cast(@id as varchar(10))

    insert into @tab (id, word) values (10000000000, 'test')
    
    set @id = (select top(1) object_id from sys.objects order by object_id desc)
    print 'Check-ID #3: ' + cast(@id as varchar(10))

    select * from @tab
END

C# Code for calling the procedure with error handling:

public static void Test()
{
    try
    {
        using (SqlConnection con = new SqlConnection(Properties.Settings.Default.TestConnection))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("___PROVOKE_ERRORS", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
    }
    catch (SqlException ex)
    {
        DisplaySqlErrors(ex);
    }
}

private static void DisplaySqlErrors(SqlException exception)
{
    for (int i = 0; i < exception.Errors.Count; i++)
    {
        Console.WriteLine("Index #{0} - {1}", i, exception.Errors[i].ToString());
    }
}

Messages when calling the procedure in Management Studio:

Check-ID #1: 3
Msg 8152, Level 16, State 30, Procedure ___PROVOKE_ERRORS, Line 15 [Batch Start Line 0]
String or binary data would be truncated.
The statement has been terminated.
Check-ID #2: 1044210445
Msg 8115, Level 16, State 2, Procedure ___PROVOKE_ERRORS, Line 20 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
Check-ID #3: 2146991621

Messages when calling the procedure in .NET application:

Index #0 - System.Data.SqlClient.SqlError: String or binary data would be truncated.
Index #1 - System.Data.SqlClient.SqlError: Arithmetic overflow error converting expression to data type int.
Index #2 - System.Data.SqlClient.SqlError: Check-ID #1: 3
Index #3 - System.Data.SqlClient.SqlError: The statement has been terminated.
Index #4 - System.Data.SqlClient.SqlError: Check-ID #2: 1044210445
Index #5 - System.Data.SqlClient.SqlError: The statement has been terminated.
Index #6 - System.Data.SqlClient.SqlError: Check-ID #3: 2146991621

Especially an error message and the corresponding "The statement has been terminated" are not in order.

M. Bauer
  • 199
  • 2
  • 9
  • 1
    why does this matter? These are different error messages produced by different things, they are going to be different. I'm not sure why the "ordering" (though these just seem to be different no in a different order) matters? – Liam Aug 04 '20 at 14:32
  • I was expecting to get something for logging which is similar to Management Studio. – M. Bauer Aug 04 '20 at 14:36
  • I am not sure but please try. you may need to deal with Sql InfoMessage. Check link - https://stackoverflow.com/questions/7663844/c-sharp-handle-on-sql-server-message-output and https://www.tf3604.com/2016/03/01/capturing-output-from-sql-server-using-c/ . this could help you. – SSD Aug 04 '20 at 14:47
  • @SSD: In real applications the caught exception will go to a logger which has no access to the SqlConection, so the `InfoMessage` event cannot be used. – M. Bauer Aug 04 '20 at 15:14

0 Answers0