Error Handling In SQL server 7.0

[13415]

Hi
How ,we will handle all unknown error which may come at run-time in SQL server stored procedure. We can't use SET_XACT_ABORT ON option. Because it will not allow to capture the error message.
What is the best way to for error handling in SQL server 7.0 stored procedure running s a batch job.

Please Help Urgently.

Thanks

 

[tlbroadbent]

Check the following links...

http://www.sqlteam.com/item.asp?ItemID=2463

http://www.sqlteam.com/item.asp?ItemID=2841

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[13415]

Thanks a lot , but this article don't discuss how to handle unknown errors inside a stored procedure. The first article mainly discuss how to customize error messages. second article deal with error handling from client.
I want to know something wich i can use in all error condition.

 

[tlbroadbent]

I pointed you to the articles to get a general idea on handling errors. Error handling in T-SQL is weak. You use @@error to capture an error number but you have to capture it immediately after every statement where an error can occur. There is no function that I know of to capture the error message. Maybe someone else knows of a function or method.

You can use the error number to search the sysmessages table for an error description. However, the message will contain placeholder variables rather than substituted values.

Here is an example of searching the sysmessages table with a captured error number.

-- Sample error handler
-- Create temp table with unique contraint
create table #t(col1 int unique)
-- Declare variables for error number and message
declare @err int, @msg varchar(8000)

-- Insert value into table
insert #t values (1)

-- Attempt to insert value again
insert #t values (1)

-- Capture error number
set @err=@@error

-- check if error occurred
If @err<>0
Begin
-- Find error message
Select
@msg='Error number: ' + ltrim(str(@err)) +
' Error message: ' + description
From master..sysmessages
Where error=@err

-- Display error message
-- could also return a record set
-- or return the variable to a calling
-- procedure or program
Print ''
Print 'An error occured'
print @msg

-- place return here to exit SP

End

-- cleanup
Drop table #t

Here are the results of running this script in Query Analyzer on SQL 2000. Note that QA displays the error message and then the code prints the error message with variables instead of values.

(1 row(s) affected)

Server: Msg 2627, Level 14, State 2, Line 11
Violation of UNIQUE KEY constraint 'UQ__#t__7E6CC920'. Cannot insert duplicate key in object '#t______________00000000000D'.
The statement has been terminated.

An error occured
Error number: 2627 Error message: Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.