How do I find out the _exact_ error from SQL Server

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 understan开发者_JAVA百科dable way.

---

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

---

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;
    }