SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can focus more on IT business strategy development and less on what needs to happen when errors occur. In SQL Server 2005, @@ERROR variable is no longer needed after every statement executed, as was the case in SQL Server 2000. SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.
Functions to be used in CATCH block are :
- ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
- ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
- ERROR_STATE: returns the state number of the error.
- ERROR_LINE: returns the line number where the error occurred.
- ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
- ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.
Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH
The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.
Example of TRY…CATCH:
BEGIN TRY
DECLARE @X INT
---- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'
Above code will return following result:
Error Detected
Command after TRY/CATCH blocks
If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.
Limitation of TRY…CATCH:
- Compiled errors are not caught.
- Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
- Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.
For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.
Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL
pinaldave
We have found the try/catch functionality extremeley useful, but now I seem to be stuck into a tricky situation with nested cursors.
What if there is an error in crsSub1 or crsSub2?
Where shall I declare, open, close and deallocate cursors when I don´t know where an error might occur??
I cannot close a cursor “on chance”, not knowing if it exists (will cause runtime error) and the same counts for opening a cursor “on chance”.
Shall I use local variables to keep track of what cursors that are open or is there built in functions that I can use??
Example:
declare crsMain local for select Pid from Person
open crsMain
while (1=1)
begin
fetch crsMain into @Pid
begin tran
begin try
— Nested subcursor Sub1
declare crsSub1 local for select * from Sub1
where Pid = @Pid
open crsSub1
insert…
update…
delete…
close crsSub1
deallocate crsSub1
— Nested subcursor Sub2
declare crsSub2 local for select * from Sub2
where Pid = @Pid
open crsSub2
insert…
update…
delete…
close crsSub2
deallocate crsSub2
commit
end try
begin catch
rollback
— do some error logging…
end catch
end
close crsMain
deallocate crsMain
Hi All,
You Can use the Cursor_Status() function to check the status of the cursor in the CATCH Block and write the code accordingly
For full detail go to bookonline topic
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/3a4a840e-04f8-43bd-aada-35d78c3cb6b0.htm
This is a catch block that I created that takes care of a cursor called “file_cursor”. probably could be a little more robust, but it does the trick:
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
PRINT ‘TRANSACTION ABORTED’
END
PRINT CURSOR_STATUS(’global’, ‘file_cursor’)
IF CURSOR_STATUS(’global’,'file_cursor’) > 0
BEGIN
CLOSE file_cursor
DEALLOCATE file_cursor
PRINT ‘The cursor has been closed and deallocated’
END
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @ErrorState < 1
SET @ErrorState = 1
— Use RAISERROR inside the CATCH block to return error
— information about the original error that caused
— execution to jump to the CATCH block.
–SET @ErrorMessage = ‘Procedure BLAH: ‘ + @ErrorMessage
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState — State.
);
END CATCH
[...] function in new feature of SQL Server 2005 TRY…CATCH. When asked for explanation he suggested SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling article as excuse suggesting that I did not give example of RAISEERROR with TRY…CATCH. We all [...]
Nice work man keep it up..
Nisheeth Pandya
Can we use BEGIN CATCH etc.. in a trigger?
Hi,
Is Try catch block is an alternative of @@raiseerror that we used in SQL 2000.
your article was extremely useful for my sql dba telephonic interview. I need answers for few questions where i was not sure.
1. How will you detect that a deadlock occured in SQL server 2000 and how will you resolve it ?
2.How many stored procedures can be written in a single crystal report?
Hi,
I want to use 2 try blocks in same stored proc. If the FIRST try block fails it goes to catch..suppose.. if my SECOND block fails, whether the first TRY block transaction gets rolledback or not?
this is my scenario
// success
begin tran
begin try
insert1
insert2
insert3
end try
begin catch
rollback
end catch
end try
commit tran
//failure
begin tran
begin try
insert1
insert2
insert3
end try
begin catch
rollback
end catch
end try
commit tran
Hi,
I want to use try-catch block against any user defined condition. say I am ising an IF block to satisfy some conditions . If it will dissatisfy, then I want to go to CATCH block. Is it possible?
BEGIN TRY
IF (@variable between 1 AND 8) –condition as per client emand) –
error produced
END TRY
BEGIN CATCH
END CATCH
I am trying to write the exception details in the text file.
What will be the faster way ?
Kamleshkumar Gujarathi.
[...] SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling [...]
Hi,
If I try to drop a constraint which does not exist in the database by using the below command I get the error message
Msg 3728, Level 16, State 1, Line 1
‘FK_ReferencedWorkspace_SchemaVersion1′ is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
However if I have the same code enclosed within a try .. catch block with the statement
select ERROR_NUMBER() as ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() as ErrorMessage
it only returns me the second error as
“Could not drop constraint. See previous errors.”
How do I get the full error message so that I can trobleshoot easily OR is this a limitation of SQL Server 2005
Thanks
Ryan
I have a problem in SQL 2000 server because I need something like try .. catch.
The problem is.
SELECT tic.cod_record_poliza,tic.cod_ramo
FROM tISO_Claim tic
WHERE cod_record_poliza = 99
Error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value ‘UP01′ to a column of data type int.
Note: This error is in a select statement because the column name cod_record_poliza is varchar and in this case the values is 99 instead of ‘99′.
—>>> I need to detect this error. <<<<——-
The question is: How could I resolve this error?
Hi Perez,
That is not an issue with Select. this issue with “WHERE”.
You can write the query as follows:
SELECT tic.cod_record_poliza,tic.cod_ramo
FROM tISO_Claim tic
WHERE cod_record_poliza = ‘99′
Let me know if it helps you.
Thanks,
Tejas
Hi All,
I have a situation in my Trigger.
My trigger is for update on a Table, whenever there is an update it has to fire the trigger and place the data in 2 Servers one is local and another is Remote server. what i need is sometimes my remote server goes offline, thogh the trigger firing and and i am missing some data. what i want is if is there any problem with servers or DB still it has to fire the trigger and it should notify me with a mail that there was a problem with remote server or DB, but still it has to do all the tasks it is suppose to do. so implemented try catch bolck in catch block i wrote like this
END TRY
Begin Catch
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode 0) GOTO PROBLEM
End Catch
PROBLEM:
IF (@intErrorCode 0)
BEGIN
declare @body1 varchar(100)
set @body1 = ‘Server :’+@@servername+ ‘Error Message:’+ERROR_MESSAGE()
EXEC msdb.dbo.sp_send_dbmail @recipients=’mailid’,
@subject = ‘My Mail Test’,
@body = @body1,
@body_format = ‘HTML’ ;
END
while i am trying get some exception trigger is not getting fired i have to rollback my operation.
Is there anyway i canget fired my trigger though there is an exception but notifying me with a mail. Any Help….
@Reddy,
Instead of waiting for trigger to execute SQL statement on remote sql server (which is offline) and fail and then send an email… why don’t you simple check if remote server or remote database is online. If it is online perform action, if it not online, then send email. something like this.
Inside trigger you can add a check like this,
if (condition to check if remote server database is online)
begin
perform what ever your action you want to perform.
end
else
begin
xp_sendemail……
end
This will definitely not rollback your transaction.
If you need more help let me know. Also provide details if you are using linked server to connect to remote server.
~ IM.
Nice one….
Imran
Thanks for your quick reply.
yes i am using linked server. I implemented sqlmail on my local server and i am getting mails.
As i mplemented Try Catch in my trigger and we know that not all the errors will be cathed in the catch block. so better i implement the the way you suggested.
How do we check that remote server is online or not, is there any code snippet you have
part1: if (condition to check if remote server database is online)
begin
perform what ever your action you want to perform.
end
part2: else
begin
xp_sendemail……
end
I am okay with the 2nd part and what should i write for part1? is there any system stored procedure to do that in sql2k5 as i am using sql2k5. Reply will be appreciated.
Thanks in advance.