Does SQL Server 2005 pass error message numbers back to the asp.net application?

Question

I'd like to get the message number and severity level information from SQL Server upon execution of an erroneous query.

For example, when a user attempts to delete a row being referenced by another record, and the cascade relationship is "no action", I'd like the application to be able to check for error message 547 ("The DELETE statement conflicted with the REFERENCE constraint...") and return a user friendly and localized message to the user.

When running such a query directly on SQL Server, the following message is printed:

Msg 547, Level 16, State 0, Line 1
<Error message...>

In an Asp.Net app is this information available in an event handler parameter or elsewhere?

Also, I don't suppose anyone knows where I can find a definitive reference of SQL Server message numbers?

Answer 1

try this to see what all the messages are

SELECT *
FROM master.dbo.sysmessages

Answer 2

Sure - check out the SqlException that gets thrown is something goes wrong on SQL Server.

It contains a collection of SqlError elements, which contain a bunch of properties, including

• Class Gets the severity level of the error returned from SQL Server.
• LineNumber Gets the line number within the Transact-SQL command batch or stored procedure that contains the error.
• Message Gets the text describing the error.
• Number Gets a number that identifies the type of error.
• Procedure Gets the name of the stored procedure or remote procedure call (RPC) that generated the error.
• Server Gets the name of the instance of SQL Server that generated the error.
• Source Gets the name of the provider that generated the error.
• State Gets a numeric error code from SQL Server that represents an error, warning or "no data found" message.

Answer 3

SQL Server error messages are located in master.dbo.sysmessages

In ASP.NET if you catch the Exception as a SQLException then the error number and message will be available.

I think it is good practice to iterate through the SQL errors as there may be more than one

(untested)

try
{
// Do some stuff here
}
catch (SQLException sqlex)
{
foreach (SqlError error in sqlex.Errors)
{
Console.WriteLine(error.Number.ToString());
Console.WriteLine(error.Message.ToString());
}
}
catch (Exception ex)
{
}