Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Unhappy Unanswered: Error handing in MS SQL Server

    How can I get more specific information for an error using the @@ERROR
    global variable in MS SQL Server?
    For example:
    Let's say I have a table with a FOREIGN KEY column defined on it AND another column which has a CHECK CONSTRAINT defined on it.
    If any of these two constraints are violated, @@ERROR will contain the value 547. This does not tell me the actual object(s) that the error pertains to.
    The error string returned does contain this information, but the messages are not very helpful for the average user. I don't think that parsing individual error message strings is the way to go, so does anyone have any ideas on how to proceed.

    PS: Most of the time, I make all the necessary checks manually within the query and return custom error codes that are known within the client application. However, this causes some overhead since SQL Server checks everything all over again when I perform the actual INSERT or UPDATE.

    Thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by afx2029
    This does not tell me the actual object(s) that the error pertains to.
    Well That's not really true..it does...it's just sort of meaningless if you let sql define the constraint object

    However, this causes some overhead since SQL Server checks everything all over again when I perform the actual INSERT or UPDATE.
    Yes it does...and why you shouldn't do it.

    When you define the object, define the object with a meaningful name.

    Have the front end parse the message out and determine what to display. If it can't figure it out, then use the original message.

    But you will definetly need a standard.

    Cut and paste this to see what happens

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(
    	  Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY
    	, Col2 char(1))
    GO
    
    CREATE TABLE myTable00(
    	  Col1 int NOT NULL
    	, Col2 char(1) NOT NULL
    	, CONSTRAINT myTable99_PK PRIMARY KEY (Col1, Col2)
    	, CONSTRAINT myTable99_FK_myTable99_Col1 FOREIGN KEY (Col1) REFERENCES myTable99(Col1)
    	, CONSTRAINT myTable99_CK_Col2_Y_N CHECK (Col2 IN ('Y','N')))
    GO
    
    INSERT INTO myTable99(Col2) 
    SELECT 'X' UNION ALL SELECT 'Y' UNION ALL SELECT 'Z'
    GO
    
    DECLARE @Error int, @RowCount int
    
    INSERT INTO myTable00(Col1, Col2)
    SELECT 4,'Y'
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    SELECT '@Error = ' + CONVERT(varchar(5),@Error), '@RowCount = ' + CONVERT(varchar(5),@Rowcount)
    
    INSERT INTO myTable00(Col1, Col2)
    SELECT 1,'X'
    SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    SELECT '@Error = ' + CONVERT(varchar(5),@Error), '@RowCount = ' + CONVERT(varchar(5),@Rowcount)
    GO
    
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •