Click here to monitor SSC
  • Av rating:
  • Total votes: 164
  • Total comments: 28
Grant Fritchey

SQL Server Error Handling Workbench

20 February 2007

/*
Error handling in SQL Server breaks down into two very distinct
situations: you're handling errors because you're in SQL Server 2005
or you're not handling errors because you're in SQL Server 2000. 
What's worse, not all errors in SQL Server, either version, can be
handled. I'll specify where these types of errors come up in each 
version.

The different types of error handling will be addressed in two 
different sections. 'll be using two different databases for the 
scripts as well, [pubs] for SQL Server 2000 and [AdventureWorks]
for SQL Server 2005.

I've broken down the scripts and descriptions into sections. Here is
a Table of Contents to allow you to quickly move to the piece of code
you're interested in. Each piece of code will lead with the server 
version on which it is being run. In this way you can find the section
and the code you want quickly and easily.

As always, the intent is that you load this workbench into Query Analyser
or Management Studio and try it out for yourself! The workbench script
is available from the CODE DOWNLOAD link above. 

GENERATING AN ERROR
SEVERITY AND EXCEPTION TYPE
TRAP AN ERROR
USING RAISERROR
RETURNING ERROR CODES FROM STORED PROCEDURES
TRANSACTIONS AND ERROR TRAPPING
EXTENDED 2005 ERROR TRAPPING
*/

/*SQL Server 2000 - GENERATING AN ERROR */
---------------------------------------

USE pubs
GO
UPDATE dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654'

/* This will generate an error:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint 
        "CK__authors__zip__7F60ED59".
The conflict occurred in database "pubs", 
        table "dbo.authors", column 'zip'.


SQL Server 2005 - GENERATING AN ERROR */
---------------------------------------

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET MaritalStatus 'H'
WHERE EmployeeID 100;

/* This generates a familiar error:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint 
        "CK_Employee_MaritalStatus".
The conflict occurred in database "AdventureWorks",
   table "HumanResources.Employee", column 'MaritalStatus'.
The statement has been terminated.


 SQL Server 2000 AND 2005 - ERROR SEVERITY AND EXCEPTION TYPE
--------------------------------------------------------------

The error message provides several pieces of information:

Msg    A message number identifies the type fo error. Can up to the 
      value of 50000.    From that point forward custom user defined 
      error messages can be defined.

Level  The severity level of the error.

      10 and lower are informational.
      11-16 are errors in code or programming, like the error above.
      Errors 17-25 are resource or hardware errors.
      Any error with a severity of 20 or higher will terminate the 
      connection (if not the server).

Line   Defines which line number the error occurred on and can come 
      in extremely handy when troubleshooting large scripts or stored 
      procedures.

Message
Text   The informational message returned by SQL Server.

Error messages are defined and stored in the system table sysmessages.*/


/*SQL Server 2000 - CATCH AN ERROR
----------------------------------

SQL Server 2000 does not allow us to stop this error being returned, but
we can try to deal with it in some fashion. The core method for 
determining if a statement has an error in SQL Server 2000 is the 
@@ERROR value. When a statement completes, this value is set.

If the value equals zero(0), no error occured. Any other value was the 
result of an error. 

The following TSQL will result in the statement 'A constraint error has 
occurred' being printed,as well as the error.

*/

USE pubs
GO
UPDATE dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654'
IF @@ERROR 547
    
PRINT 'A constraint error has occurred'
GO

-- @@ERROR is reset by each and every statement as it occurrs.
-- This means that if we use the exact same code as above, but check the 
--  @@ERROR function
-- a second time, it will be different.

UPDATE dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654'
IF @@ERROR 547
    
PRINT 'A constraint error has occurred. Error Number:' 
      
CAST(@@ERROR AS VARCHAR)
GO

-- You will see the error number as returned by the @@ERROR statement
-- as being zero(0), despite the fact that we just had a clearly 
-- defined error.

-- The problem is, while the UPDATE statement did in fact error out, 
-- the IF statement executed flawlessly and @@ERROR is reset after each
-- and every statement in SQL Server. 

-- In order to catch and keep these errors, you need to capture the 
-- @@ERROR value after each execution.

DECLARE @err INT
UPDATE 
dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654'
SET @err @@ERROR
IF @err 547
    
PRINT 'A constraint error has occurred. Error Number:' 
      
CAST(@err AS VARCHAR)
GO

-- Now we can capture the error number and refer to it as often as
-- needed within the code. 


/* SQL Server 2005 - CATCH AN ERROR
------------------------------------

While @@ERROR is still available in SQL Server 2005, a new syntax has 
been added to the T-SQL language, as implemented by Microsoft:
TRY... CATCH.

This allows us to finally begin to perform real error trapping. 
*/

BEGIN TRY 
    
UPDATE HumanResources.Employee
    
SET MaritalStatus 'H'
    
WHERE EmployeeID 100;
END TRY
BEGIN CATCH
    
PRINT 'Error Handled';
END CATCH

/*
While there is an error encountered in the code, none is returned to
the calling function.
In fact, all that will happen in this case is the string 'Error 
Handled' is returned to the client.

We have actually performed the function of error trapping within TSQL.

There are a number of issues around the use of TRY...CATCH that have 
to be dealt with, which we will cover later. For example, simply having
a TRY...CATCH statement is not enough.

Consider this example:
*/

    
UPDATE HumanResources.Employee
    
SET ContactID 19978
    
WHERE EmployeeID 100;
BEGIN TRY 
    
UPDATE HumanResources.Employee
    
SET MaritalStatus 'H'
    
WHERE EmployeeID 100;
END TRY
BEGIN CATCH
    
PRINT 'Error Handled';
END CATCH


-- The second error is handled, but the first one is not and we would 
-- see this error returned to client application:

Msg 547LEVEL 16State 0Line 1
The 
UPDATE statement conflicted WITH the FOREIGN KEY CONSTRAINT 
      
"FK_Employee_Contact_ContactID"
The conflict occurred 
IN DATABASE "AdventureWorks" 
      
TABLE "Person.Contact" COLUMN 'ContactID'.

-- To elminate this problem place multiple statements within the 
-- TRY statement.

 /* SQL Server 2000 - USING RAISERROR
------------------------------------

The RAISERROR function is a mechanism for returning to calling 
applications errors with your own message.
It can use system error messages or custom error messages. The 
basic syntax is easy:
*/

RAISERROR ('You made a HUGE mistake',10,1)

/*
To execute RAISERROR you'll either generate a string, up to 400
characters long, for the message, or you'll access a message by 
message id from the master.dbo.sysmessages table.

You also choose the severity of the error raised. Severity levels
used in RAISERROR will behave exactly as if the engine itself had 
generated the error. This means that a SEVERITY of 20 or above
will terminate the connection. The last number is an arbitrary value
that has to be between 1 and 127. 
*/

-- You can format the message to use variables. This makes it more 
-- useful for communicating errors:

RAISERROR('You broke the server: %s',10,1,@@SERVERNAME)

-- You can use a variety of different variables. You simply have to 
-- declare them by data type and remember that, even with variables, 
-- you have a 400 character limit.
-- You also have some formatting options.

--Unsigned Integer
RAISERROR('The current error number: %u',10,1,@@ERROR)

--String
RAISERROR('The server is: %s',10,1,@@SERVERNAME)

--Compound String & Integer & limit length of string to first 5 
--characters
RAISERROR('The server is: %.5s. The error is: %u',10,1,
      
@@SERVERNAME,@@ERROR)

--String with a minimum and maximum length and formatting to left
RAISERROR('The server is: %-7.3s',10,1,@@SERVERNAME)

/*
A few notes about severity and status. Status can be any number up 
to 127 and you can make use of it on your client apps. Setting the 
Status to 127 will cause ISQL and OSQL to return the error number 
to the operating environment.
*/

-- To get the error into the SQL Server Error Log
RAISERROR('You encountered an error',18,1WITH LOG

-- To immediately return the error to the application
RAISERROR('You encountered an error',10,1WITH NOWAIT

-- That also flushes the output buffer so any pending PRINT statements,
-- etc., are cleared.

-- To use RAISERROR as a debug statement
RAISERROR('I made it to this part of the code',0,1)


/* SQL SERVER 2005 - USING RAISERROR
------------------------------------

The function of RAISERROR in SQL Server 2005 is largely the same as for
SQL 2000.However, instead of 400 characters, you have 2047. If you use
2048 or more, then 2044 are displayed along with an elipsis.

RAISERROR will cause the code to jump from the TRY to the CATCH block.

Because of the new error handling capabilities, RAISERROR can be called
in a more effecient manner in SQL Server 2005. This from the Books 
Online:
*/
BEGIN TRY 
    
RAISERROR('Major error in TRY block.',16,1);
END TRY
BEGIN CATCH
    
DECLARE @ErrorMessage NVARCHAR(4000),
        
@ErrorSeverity INT,
        
@ErrorState INT;

    
SET @ErrorMessage ERROR_MESSAGE();
    
SET @ErrorSeverity ERROR_SEVERITY();
    
SET @ErrorState ERROR_STATE();

    
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;


/* SQL Server 2000 - RETURNING ERROR CODES FROM STORED PROCEDURES
-----------------------------------------------------------------

Stored procedures, by default, return the success of execution as 
either zero or a number representing the failure of execution, but
not necessarily the error number encountered.
*/

CREATE PROCEDURE dbo.GenError
AS
UPDATE 
dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654' 
GO

DECLARE @err INT
EXEC 
@err GenError
SELECT @err

-- This will cause an error and the SELECT statement will return a 
-- non-zero value. On my machine, -6. In order take control of this,
-- modify the procedure as follows:

ALTER PROCEDURE dbo.GenError
AS
DECLARE 
@err INT
UPDATE 
dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654'
SET @err @@ERROR
IF @err <> 0
    
RETURN @err
ELSE
    RETURN 
0
GO

DECLARE @err INT
EXEC 
@err GenError
SELECT @err

-- This time the SELECT @err statement will return the 547 error number
-- in the results. With that, you can begin to create a more 
-- appropriate error handling routine that will evolve into a coding 
-- best practice within your organization.

ALTER PROCEDURE dbo.GenError
AS
DECLARE 
@err INT
UPDATE 
dbo.authors
SET zip '!!!'
WHERE au_id '807-91-6654'
SET @err @@ERROR
IF @err <> 0
    
BEGIN
        IF 
@err 547
            
RAISERROR('Check Constraint Error occurred',16,1)
        
ELSE
            RAISERROR
('An unspecified error has occurred.',10,1)
        
RETURN @err
    
END
ELSE
    RETURN 
0
GO


/* SQL Server 2005 - RETURNING ERROR CODES FROM STORED PROCEDURES
-----------------------------------------------------------------

In order to appropriately handle errors you to know what they are.
You may also want to return the errors to the calling application. 
A number of new functions have been created so that you can 
appropriately deal with different errors, and log, report,
anything you need, the errors that were generated.
*/

CREATE PROCEDURE GenErr
AS
    BEGIN 
TRY
        
UPDATE HumanResources.Employee
        
SET ContactID 19978
        
WHERE EmployeeID 100;
    
END TRY
    
BEGIN CATCH
        
PRINT 'Error Number: ' CAST(ERROR_NUMBER() AS VARCHAR);
        
PRINT 'Error Message: ' ERROR_MESSAGE();
        
PRINT 'Error Severity: ' CAST(ERROR_SEVERITY() AS VARCHAR);
        
PRINT 'Error State: ' CAST(ERROR_STATE() AS VARCHAR);
        
PRINT 'Error Line: ' CAST(ERROR_LINE() AS VARCHAR);
        
PRINT 'Error Proc: ' ERROR_PROCEDURE();
    
END CATCH
GO

DECLARE @err INT;
EXEC @err GenErr;
SELECT @err;

-- When you run the code above, you should receive this on the client,
-- in the message, with a non-zero number in the result set:
(0 row(saffected)
Error Number: 547
Error Message: The 
UPDATE statement conflicted WITH the 
           
FOREIGN KEY CONSTRAINT
          
"FK_Employee_Contact_ContactID" 
           The conflict occurred 
IN DATABASE
           
"AdventureWorks" TABLE "Person.Contact" 
           
COLUMN 'ContactID'.
Error Severity: 16
Error State: 0
Error Line: 4
Error Proc: GenErr

-- In other words, everything you need to actually deal with errors 
-- as they occur.
-- You'll also notice that the procedure returned an error value 
-- (non-zero) even though we didn't specify a return code. You can
-- still specify a return value as before if you don't want to leave
-- it up to the engine.


/* SQL Server 2000 - TRANSACTIONS AND ERROR TRAPPING
----------------------------------------------------

The one area of control we do have in SQL Server 2000 is around the 
transaction. In SQL Server 2000 you can decide to rollback or not, 
those are your only options. You need to make decision regarding
whether or not to use XACT_ABORT. Setting it to ON will cause an 
entire transaction to terminate and rollback in the event of any 
runtime error. if you set it to OFF, then in some cases you can 
rollback the individual statement within the transaction as opposed 
to the entire transaction.

Modify the procedure to handle transactions:
*/
 
ALTER PROCEDURE dbo.GenError
AS
DECLARE 
@err INT

BEGIN TRANSACTION

    UPDATE 
dbo.authors
    
SET zip '90210'
    
WHERE au_id '807-91-6654'
    
SET @err @@ERROR
    
IF @err <> 0
        
BEGIN
            IF 
@err 547
            
PRINT 'A constraint error has occurred.'
            
ELSE
                PRINT 
'An unspecified error has occurred.'
            
ROLLBACK TRANSACTION
            RETURN 
@err
        
END
        
    UPDATE 
dbo.authors
    
SET zip '!!!'
    
WHERE au_id '807-91-6654'
    
SET @err @@ERROR
    
IF @err <> 0
        
BEGIN
            IF 
@err 547
                
PRINT 'A constraint error has occurred.'
            
ELSE
                PRINT 
'An unspecified error has occurred.'
            
ROLLBACK TRANSACTION
            RETURN 
@err
        
END
    ELSE
        BEGIN
            COMMIT TRANSACTION
            RETURN 
0
        
END
GO

DECLARE @err INT
EXEC 
@err GenError

SELECT zip 
FROM dbo.authors
WHERE au_id '807-91-6654'

/*
Since the above code will generate an error on the second statement,
the transaction is rolled back as a unit. Switch to the results in 
order to see that the zip code is, in fact, still 90210. If we wanted
to control each update as a seperate statement, in order to get one 
of them to complete, we could encapsulate each statement in a 
transaction:
*/

ALTER PROCEDURE dbo.GenError
AS
DECLARE 
@err INT

BEGIN TRANSACTION

    UPDATE 
dbo.authors
    
SET zip '90210'
    
WHERE au_id '807-91-6654'
    
SET @err @@ERROR
    
IF @err <> 0
        
BEGIN
            IF 
@err 547
            
PRINT 'A constraint error has occurred.'
            
ELSE
                PRINT 
'An unspecified error has occurred.'
            
ROLLBACK TRANSACTION
            RETURN 
@err
        
END
    ELSE
        COMMIT TRANSACTION

BEGIN TRANSACTION    
    UPDATE 
dbo.authors
    
SET zip '!!!'
    
WHERE au_id '807-91-6654'
    
SET @err @@ERROR
    
IF @err <> 0
        
BEGIN
            IF 
@err 547
                
PRINT 'A constraint error has occurred.'
            
ELSE
                PRINT 
'An unspecified error has occurred.'
            
ROLLBACK TRANSACTION
            RETURN 
@err
        
END
    ELSE
        BEGIN
            COMMIT TRANSACTION
            RETURN 
0
        
END
GO

DECLARE @err INT
EXEC 
@err GenError

SELECT zip 
FROM dbo.authors
WHERE au_id '807-91-6654'

/*
In this case then, the return value will be '90210' since the first 
update statement will complete successfully. Be sure that whatever 
mechanism you use to call procedures does not itself begin a
transaction as part of the call or the error generated will result 
in a rollback, regardless of the commit within the procedure. In the
next example, we'll create a transaction that wraps the other two 
transactions, much as a calling program would. If we then check for
errors and commit or rollback based on the general error state, it's
as if the inner transaction that was successful never happened, as the
outer transaction rollback undoes all the work within it.
*/

ALTER PROCEDURE dbo.GenError
AS
DECLARE 
@err1 INT
DECLARE 
@err2 INT

BEGIN TRANSACTION
    BEGIN TRANSACTION

        UPDATE 
dbo.authors
        
SET zip '90211'
        
WHERE au_id '807-91-6654'
        
SET @err1 @@ERROR
        
IF @err1 <> 0
            
BEGIN
                IF 
@err1 547
                
PRINT 'A constraint error has occurred.'
                
ELSE
                    PRINT 
'An unspecified error has occurred.'
                
ROLLBACK TRANSACTION
                RETURN 
@err1
            
END
        ELSE
            COMMIT TRANSACTION

    BEGIN TRANSACTION    
        UPDATE 
dbo.authors
        
SET zip '!!!'
        
WHERE au_id '807-91-6654'
        
SET @err2 @@ERROR
        
IF @err2 <> 0
            
BEGIN
                IF 
@err2 547
                    
PRINT 'A constraint error has occurred.'
                
ELSE
                    PRINT 
'An unspecified error has occurred.'
                
ROLLBACK TRANSACTION
                RETURN 
@err2
            
END
        ELSE
            BEGIN
                COMMIT TRANSACTION
                RETURN 
0
            
END
IF 
(@err1 <> 0) OR (@err2 <> 0)
    
ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION
GO

DECLARE @err INT
EXEC 
@err GenError

SELECT zip 
FROM dbo.authors
WHERE au_id '807-91-6654'


/* SQL Server 2005 - TRANSACTIONS AND ERROR TRAPPING
---------------------------------------------------

The new error handling changes how transactions are dealt with. 
You can now check the transaction state using XACT_STATE() function.
Transactions can be:

    closed (equal to zero (0))
    open but unable to commit (-1)
    open and able to be committed (1).

From there, you can make a decision as to whether or not a transaction
is committed or rolled back. XACT_ABORT works the same way.
*/

ALTER PROCEDURE GenErr
AS
    BEGIN 
TRY
        
BEGIN TRAN
            UPDATE 
HumanResources.Employee
            
SET ContactID 1/0
            
WHERE EmployeeID 100;
        
COMMIT TRAN
    END 
TRY
    
BEGIN CATCH
        
IF (XACT_STATE()) -1
        
BEGIN
            ROLLBACK TRAN
;
            
RETURN ERROR_NUMBER();
        
END
        ELSE IF 
(XACT_STATE()) 1
        
BEGIN
      
--it now depends on the type of error or possibly the line number 
      --of the error
            
IF ERROR_NUMBER() 8134
            
BEGIN
                ROLLBACK TRAN
;
                
RETURN ERROR_NUMBER();
            
END
            ELSE
            BEGIN
                COMMIT TRAN

                
RETURN ERROR_NUMBER();
            
END
        END
    END 
CATCH
GO

DECLARE @err INT;
EXEC @err GenErr;
SELECT @err;

/* SQL Server 2005 - EXTENDED 2005 ERROR TRAPPING
----------------------------------------------

With the new TRY...CATCH construct, it's finally possible to do things
about errors, other than just return them. Take for example the dreaded 
deadlock. Prior to SQL Server 2005, the best you could hope for was to 
walk through the error messages stored in the log recorded by setting 
TRACEFLAG values. Now, instead, you can set up a retry mechanism to 
attempt the query more than once. 
*/

ALTER PROCEDURE GenErr
AS
DECLARE 
@retry AS tinyint
      
@retrymax AS tinyint
      
@retrycount AS tinyint;

SET @retrycount 0;
SET @retrymax 2;
SET @retry 1;

WHILE @retry AND @retrycount <= @retrymax
BEGIN
    SET 
@retry 0;
    
BEGIN TRY
        
UPDATE HumanResources.Employee
        
SET ContactID ContactID
        
WHERE EmployeeID 100;
    
END TRY
    
BEGIN CATCH
        
IF (ERROR_NUMBER() 1205)
        
BEGIN
            SET 
@retrycount @retrycount 1;
            
SET @retry 1;
        
END
    END 
CATCH
END
GO

DECLARE @err INT;
EXEC @err GenErr;
SELECT @err;

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 164 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: related articles
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 7:22 AM
Message: These are excellent articles on dealing with errors in sql server:

http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html


Subject: SQL Server Error Handling Workbench
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 11:55 AM
Message: Very detailed!

Subject: SQL Server Error Handling Workbench
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 8:16 PM
Message: Great article!

Subject: SQL Server Error Handling Workbench
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 21, 2007 at 8:17 PM
Message: Very detailed in SQL Server 2005 error handling technique.

Subject: Article reader
Posted by: Anonymous (not signed in)
Posted on: Friday, February 23, 2007 at 3:29 AM
Message: Nicely described..Thanks.

Subject: SQL Server Error Handling Workbench
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 07, 2007 at 1:21 PM
Message: This Error Handling Work Bench is great. I have read a lot of information in relations to error trapping but I have not see much on how to trap the actual statement that causes the error. In case of Dynamic SQL how can one go about catching or trapping the statement?

Subject: Dynamic SQL
Posted by: Anonymous (not signed in)
Posted on: Tuesday, March 13, 2007 at 1:41 PM
Message: You know, I'm not sure. I'll get back to you on that one.

Subject: Thanks
Posted by: Patrick Index (view profile)
Posted on: Friday, March 16, 2007 at 9:02 AM
Message: Very clear and well written.

Subject: SQL Server Error Handling Workbench
Posted by: Anonymous (not signed in)
Posted on: Monday, May 07, 2007 at 5:22 PM
Message: Great article!

Subject: Bill,Best Post
Posted by: Bill (view profile)
Posted on: Tuesday, June 12, 2007 at 2:18 PM
Message: MSN I NIIPET
<a href="http://msn.com">MSN</a>

Subject: Just a little more help needed...
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 14, 2007 at 1:06 PM
Message: This is all good information but my problem has to do with a "severe" error that I can't seem to trap and evaluate in SQL Server 2000 (and no, I can't switch to 2005, this is on our customers' machines). And I've not been able to find info on this specific problem, even when I find informative articles such as yours.

This is essentially the statement I'd like to catch and gracefully quit if it occurs:

CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @ErrorNumber = @@ERROR

Execution never gets to the "SET @ErrorNumber = @@ERROR" statement so I can't act on it. In stead it bombs right away and gives me this error message:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is '706'.
The statement has been terminated.

When that CREATE statement is executed I'd like to gracefully exit the stored procedure (sproc) and report the error to the operator. It's not that I don't understand the error - I fully expect it with SOME of our customers - the problem is that I want to report the REASON for the error to our customers of various expertise.

I created a series of sprocs to re-create indexes in our customers' databases when we define them. Indexes were not defined before, or were defined with random names, so now I'm trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.

Now, above I said I was "essentially" executing that CREATE statement - to be more specific, I've tested all of these statements below and none of them are allowing me to trap: (Note, @SQLString is the CREATE statement above)

EXEC @ErrorNumber = sp_executesql @SQLString
PRINT @ErrorNumber, @@ERROR --<-- This print statement does nothing since it errors out.

EXEC ( @SQLString )
SET @ErrorNumber = @@ERROR --<-- This set statement does nothing since it errors out.

CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @ErrorNumber = @@ERROR --<-- This set statement does nothing since it errors out.


If you need more info, I can expand.

Subject: Just a little more help needed...
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 14, 2007 at 2:18 PM
Message: This is all good information but my problem has to do with a "severe" error that I can't seem to trap and evaluate in SQL Server 2000 (and no, I can't switch to 2005, this is on our customers' machines). And I've not been able to find info on this specific problem, even when I find informative articles such as yours.

This is essentially the statement I'd like to catch and gracefully quit if it occurs:

CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @ErrorNumber = @@ERROR

Execution never gets to the "SET @ErrorNumber = @@ERROR" statement so I can't act on it. In stead it bombs right away and gives me this error message:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is '706'.
The statement has been terminated.

When that CREATE statement is executed I'd like to gracefully exit the stored procedure (sproc) and report the error to the operator. It's not that I don't understand the error - I fully expect it with SOME of our customers - the problem is that I want to report the REASON for the error to our customers of various expertise.

I created a series of sprocs to re-create indexes in our customers' databases when we define them. Indexes were not defined before, or were defined with random names, so now I'm trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.

Now, above I said I was "essentially" executing that CREATE statement - to be more specific, I've tested all of these statements below and none of them are allowing me to trap: (Note, @SQLString is the CREATE statement above)

EXEC @ErrorNumber = sp_executesql @SQLString
PRINT @ErrorNumber, @@ERROR --<-- This print statement does nothing since it errors out.

EXEC ( @SQLString )
SET @ErrorNumber = @@ERROR --<-- This set statement does nothing since it errors out.

CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @ErrorNumber = @@ERROR --<-- This set statement does nothing since it errors out.


If you need more info, I can expand.

Subject: Bad News
Posted by: Granted (view profile)
Posted on: Thursday, June 21, 2007 at 2:07 PM
Message: That's the kind of error you just can't trap in SQL 2000. There are no options that I'm aware of. I'm sorry.

Subject: Thanks
Posted by: Anonymous (not signed in)
Posted on: Tuesday, August 14, 2007 at 7:12 PM
Message: This helped me lot. Great

Subject: Error handling.
Posted by: Anonymous (not signed in)
Posted on: Monday, February 04, 2008 at 3:02 PM
Message: Your article ... workbench was informative concise and right on the mark. It answered a ton of questions for a SQL beginer like me. I certainly appreciated your effort, and knowledge base.

If I may impose on your good nature and ask just 2 questions.

1. What are the caveats that I should be aware of when adding user-defined error messages to the master.dbo.sysmessages tables. Can I create a private sysmessages table so that I can restore the original w/o restoring my master db.

2. Is this a legitimate way to validate compliance with my application's process logic. I relaize that I can use constraints, foreign keys etc to ensure data accuracy, but what my app needs is some way to quickly establish referential integrity accross processes. EG the rsults of Process B are consistent with the results of Process A,i.e. they either confirm or invalidate each other.

Thanks again.

Subject: How to handle the error in the first sight
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 07, 2008 at 8:04 AM
Message: Really is very good. As a SQl DBA or A Developer he will learn a lot of things from this script.
Really it is very nice.

Thanks

Subject: Re: Error Handling
Posted by: Granted (view profile)
Posted on: Thursday, February 07, 2008 at 10:07 AM
Message: 1. You've pretty much hit the caveat. You have to maintain them over time. It's worse when you have multiple development teams working on different databases but all deploying to a single server. We have a 'dba' database that we put on all our servers. It has all kinds of maintenance routines & such, but it also includes a table to maintain this data.
2.If the different apps are calling different databases... nope. If they're calling the same database, you have to work with the constraint methods provided. You can add triggers, although you need to be careful with those.

Subject: re: How to handl the error in the first sight
Posted by: Granted (view profile)
Posted on: Thursday, February 07, 2008 at 10:08 AM
Message: I'm glad it was helpful.

Subject: Help
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 26, 2008 at 8:27 PM
Message: Thank you for this article. Very Informative. I've read thru it and some other articles on error trapping but i can't seem to find a solution to my problem.

I have a software (done in VB 6.0) connected to an SQL server 2003. User logs in, and the information is stored in a table (username, password, time log in, status, etc). My problem is the client-server connection is disconnected several times in a day. If this happens, the user currently using the system cant log in again because his login status is still TRUE. So, they need to call the admin user several times a day just to reset the login status of the user. My question is, is there a way I can catch that disconnection error so i can reset my login status to FALSE before i am disconnected from the SQL server? I tried using commit-rollback but to no avail. Or maybe i just dont know how to use it :(.

Thanks for your help.

Subject: Help
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 26, 2008 at 8:43 PM
Message: Thank you for this article. Very Informative. I've read thru it and some other articles on error trapping but i can't seem to find a solution to my problem.

I have a software (done in VB 6.0) connected to an SQL server 2003. User logs in, and the information is stored in a table (username, password, time log in, status, etc). My problem is the client-server connection is disconnected several times in a day. If this happens, the user currently using the system cant log in again because his login status is still TRUE. So, they need to call the admin user several times a day just to reset the login status of the user. My question is, is there a way I can catch that disconnection error so i can reset my login status to FALSE before i am disconnected from the SQL server? I tried using commit-rollback but to no avail. Or maybe i just dont know how to use it :(.

Thanks for your help.

Subject: RE: Help
Posted by: Granted (view profile)
Posted on: Tuesday, March 04, 2008 at 7:03 AM
Message: You don't really want to try to maintain connection information within the database in that manner because, as you see, the users can simply disconnect and there's nothing you can do about it.

Either just stop recording that, or, when the users log in, if the update statement that sets the record to true hits an error, catch it there.

Sorry that wasn't very helpful.

Subject: Insert..
Posted by: Anonymous (not signed in)
Posted on: Saturday, March 15, 2008 at 11:49 PM
Message: Can I use this in Insert Statement? and what does it looks like?...

Can someone use Error Handling for check constraint of au_id in pubs.dbo.authors?

Subject: Insert..
Posted by: Granted (view profile)
Posted on: Tuesday, April 08, 2008 at 6:49 AM
Message: You've got two questions there, but I don't see what you mean on either one. A little more clarification please?

Subject: Great article, some help?
Posted by: Ron (view profile)
Posted on: Tuesday, June 10, 2008 at 9:31 AM
Message: I like your article and found it useful. Where I'm continuing to struggle though is for errors in Sql 2000 involving bad data from a file feed - for example, an invalid character in an integer only field. I'd like to catch this myself so that I can flag the record number in the file that caused the problem. Am I out of luck?

Thx,

Ron

Subject: re: File Feeds
Posted by: Granted (view profile)
Posted on: Thursday, June 12, 2008 at 1:36 PM
Message: Yep.

Unfortunately 2000 just doesn't have anything approaching the sophistication that we're finally getting with 2005/2008. I'm sorry.

Subject: Code doesn't work as explained above
Posted by: espasojevic (view profile)
Posted on: Friday, August 28, 2009 at 1:22 PM
Message: Hi,

I copied and pasted code above, but at the part:
"Since the above code will generate an error on the second statement,
the transaction is rolled back as a unit. Switch to the results in
order to see that the zip code is, in fact, still 90210.",
it doesn't work as expected, no matter if the option XACT_ABORT is turned on or not. As a matter of fact, first transaction got rolled back as well, so the value is 20853!
Any idea?
Thanks

Subject: Code doesn't work as explained above
Posted by: espasojevic (view profile)
Posted on: Friday, August 28, 2009 at 1:29 PM
Message: Hi,

I copied and pasted code above, but at the part:
"Since the above code will generate an error on the second statement,
the transaction is rolled back as a unit. Switch to the results in
order to see that the zip code is, in fact, still 90210.",
it doesn't work as expected, no matter if the option XACT_ABORT is turned on or not. As a matter of fact, first transaction got rolled back as well, so the value is 20853!
Any idea?
Thanks

Subject: Code doesn't work as explained above
Posted by: espasojevic (view profile)
Posted on: Friday, August 28, 2009 at 1:55 PM
Message: Hi,

I copied and pasted code above, but at the part:
"Since the above code will generate an error on the second statement,
the transaction is rolled back as a unit. Switch to the results in
order to see that the zip code is, in fact, still 90210.",
it doesn't work as expected, no matter if the option XACT_ABORT is turned on or not. As a matter of fact, first transaction got rolled back as well, so the value is 20853!
Any idea?
Thanks

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.