How do I find out the _exact_ error from SQL Server

Question

I have an application in .NET. It updates a SQL Server database.
An exception is thrown. How do I know exactly what happened?

Understanding that it, for instance, was a foreign key violation isn't any problem. But which foreign key is.

The explaining text (exception.Message) is enough for me, as a human, since it holds (semi-) free text. But a computer shouldn't have to lower itself to human chitter chatter.

Especially since the error message might change between SQL Server versions and absolutely between installed languages. "...foreign key exception FK_Cust..." or "...främmande nyckel undantag FK_Cust..."

There is a number (a remains from the COM heydays?) that maps to a certain class of error but I haven't found the very foreign key, or index, or constraint that failed in a computer understandable way.

The error messages pre-substitution can be found by (for example) `select * from sys.messages where message_id=2627` so faced with an error such as `Violation of PRIMARY KEY constraint 'PK__#0BC6C43__3BD019960DAF0CB0'. Cannot insert duplicate key in object 'dbo.@x'. The duplicate key value is (1).` you could use that to extract the component parts for the language specific message. — Martin Smith, Mar 23 '11 at 21:10
That is exactly what I was hoping to avoid. I don't want to create a text parser for every language combined with every sqlserver version. Thanks anyway for the good example. — LosManos, Mar 24 '11 at 07:11
You wouldn't need to you would need to parse 2 string formats. The 1033 one and the one for every other language. Once you have established that error 2627 is one you are interested in and you want to know the name of the affected constraint and table these are represented by `%1` and `%3` respectively in the message. — Martin Smith, Mar 24 '11 at 11:02
@Martin - I still have to parse the text? There isn't any safer way? ( As a programmer it feels wierd to place critical exception information solely in a field intended for humans. ) — LosManos, Mar 25 '11 at 15:18
I see your point entirely but that's the only way I'm aware that it can be done at present. — Martin Smith, Mar 25 '11 at 15:19

score 4 · Answer 1 · answered Mar 23 '11 at 21:02

4

You look at the SqlException.Errors collection. Each SqlError in the collection has a Number. That number will be your exact error.

answered Mar 23 '11 at 21:02

Remus Rusanu

288,378
40
442
569

But the OP is asking how to extract the information about the error that all gets concatenated into the error message. – Martin Smith Mar 23 '11 at 21:20
And those errors can by found in the `sys.messages` system catalog view – marc_s Mar 23 '11 at 21:32
Still can't find out exact which foreign key or index or whatever that failed. – LosManos Mar 24 '11 at 07:15

score 0 · Answer 2 · edited Jul 29 '14 at 13:04

Just pass the SQL exception raised to this function that I wrote and you can also run a query to see the SQL error codes

select * from sys.sysmessages

public string getSQLExceptionMessage(SqlException ex)
{
    string message = " Default SQL Exception. ";

    switch (ex.Number)
    {
            case 4060:
                message = "Invalid Database.Check Database Name";
                break;
            case 18456:
                message = "Login Failed.Check Database Credentials";
                break;
            case 547:
                message = "Foreign Key violation.Check Database Schema";
                break;
            case 10054:
                message = "Connection To Database Refused";
                break;
            case 214:
                message = ex.Message.ToString(); ;
                break;
            case 20:
                message = ex.Message.ToString(); ;
                break;
            case 229:
                message = "Permission Denied On Object. Contact DBA";
                break;
            case 230:
                message = "Permission denied On A Column. Check permissions";
                break;
            case 235:
                message = "Cannot Convert A Char Value To Money. The Char Value Has Incorrect Syntax.";
                break;
            case 236:
                message = "The Conversion From Char Data Type To Money Resulted In A Money Overflow Error.";
                break;
            case 241:
                message = "Conversion Failed When Converting Datetime From Character String.";
                break;
            case 262:
                message = "Permission Denied In Database.";
                break;
            case 297:
                message = "User Does Not Have Permissions To Perform This Action";
                break;
            case 313:
                message = ex.Message.ToString();
                break;
            case 8144:
                message = ex.Message.ToString();//"To Many Arguments Supplied For Procedure/Function ";
                break;
            case 8146:
                message = ex.Message.ToString();//"Procedure Has No Parameters And Arguments Were Supplied ";
                break;
            case 10004:
                message = "One Or More Invalid Arguments ";
                break;
            case 18452:
                message = "Login Failed For User. User Not Associated With A Trusted SQL Server Connection";
                break;
            case 21670:
                message = "Connection To Server Failed.";
                break;
            case 2812:
                message = "Could Not Find Stored Procedure. Check Name Of Stored Procedure";
                break;
            case 14043:
                message = ex.Message.ToString();//Null Parameter Passed To Procedure
                break;
            case 15003:
                message = ex.Message.ToString();//Role Specific SP
                break;
            case 16903:
                message = ex.Message.ToString();//Incorrect Number Of Parameters
                break;
            case 16914:
                message = ex.Message.ToString();//To Many Parameters
                break;
            case 18751:
                message = ex.Message.ToString();//Wrong Number Of Parameters
                break;
            case 20587:
                message = ex.Message.ToString();//Invalid Value For Procedure
                break;
            case 20624:
                message = ex.Message.ToString();//User Not In Database
                break;
            case 21234:
                message = ex.Message.ToString();//Cannot Insert as Table Has Identity Column
                break;
            case 21343:
                message = ex.Message.ToString();//Cannot Find Stored Procedure
                break;
            default:
                message = ex.Message.ToString() + Environment.NewLine + "SQL ERROR CODE : " + ex.Number + Environment.NewLine + "Run Query For SysMessages To Check Error Details";
                break;

        }

        return message;
    }

Why use case's for the 10+ items that are just "ex.Message.ToString()"? I get that the default adds the error code to the message, but your method has a ton of bloat. — rossisdead, Mar 23 '11 at 21:52
Error 547/Foreign key violation... still doesn't say *which* FK failed. That is the problem. — LosManos, Mar 24 '11 at 07:13
547 is a general error for all check constraint - it's not certain that you're going to catch only FK violations — Maksymilian Majer, Aug 15 '12 at 09:49

How do I find out the _exact_ error from SQL Server

2 Answers2

Linked

Related