Get Specific Info From SQL Error Message 547

How do I correctly extract specific info from an sql error message number 547?

Info Required:

• Table Name
• Constraint Name
• Column Name

Code:

Try 
  ....
Catch ex As System.Data.SqlClient.SqlException 
        If ex.Number = 547 Then

        End If
End Try

Sample message:

UPDATE state开发者_StackOverflow中文版ment conflicted with COLUMN CHECK constraint 'CK_Birthdate'. The conflict occurred in database 'Northwind', table 'Employees', column 'BirthDate'.

There is no straight forward way of getting these pieces of information separately.

It all gets concatenated into the error message.

You can use select * from sys.messages where message_id=547 to see the various different language formats of the message that you would need to deal with in order to extract the constituent parts then perhaps use regular expressions with capturing groups based around this information.

In addition to queries, here's a powershell script which wraps the sys.messages queries.

http://blogs.msdn.com/b/buckwoody/archive/2009/04/30/and-the-winner-is-get-sql-server-error-messages-from-powershell.aspx

its true there is no straight way to fix this but I did this insted

var str = sqlException.Message.ToString();
var strlist = str.Split(',', StringSplitOptions.RemoveEmptyEntries);
var streplace = strlist[1];
streplace = streplace.Replace("table \"dbo.", "");
streplace = streplace.Replace("\"", ""); //this will get the data table name 
streplace = string.Concat(streplace.Select(x => Char.IsUpper(x) ? " " + x : x.ToString())).TrimStart(' ');