June 7, 2015 at 10:15 am
Hi Experts,
Try/Catch block is introduced in sql server 2005, to capture error and to stop sql server for doing any premature termination due to any error as try block if throws any error, it will catch it and will throw any meaningful error.
Wanted to know how this concept used to work in SQL 2000 or previous editions when there were no try/catch feature was available?
Secondly, do I need to still include Raiseerror along with Try/Catch block?
Wanted to check if I am running a job, and if that fails by any reason but if my code does not have any raiseerror command to throw the error, will the job still fail with error so that my alert (which I have set) can pick up the error and can throw it??
Or, only including Try/Catch alone can do the work?
Thanks.
June 7, 2015 at 11:04 am
Error handling within SQL Server 2000 is pretty limited, but there's a decent article describing what can be done here: http://www.techrepublic.com/article/understanding-error-handling-in-sql-server-2000/
As for TRY...CATCH, you do need to include code in the CATCH block to handle the error, otherwise it's silently ignored.
Whether that's PRINTing or SELECTing an appropriate error message (using ERROR_MESSAGE() and the related functions), or using RAISERROR to generate an error of the desired severity, you need to include something to handle the error in the CATCH block.
Having said that, there are several sorts of errors that are not trapped by TRY...CATCH, so you should be aware of those. They're nicely listed out at https://msdn.microsoft.com/en-us/library/ms175976.aspx in the section "Errors Unaffected by a TRY…CATCH Construct". Among the more notable non-trapped errors are errors coming from DBCC CHECK* statements.
Here's just a quick example of the various behaviors I've described:
--Raises the usual 8134, Divide by zero error encountered.
SELECT 1/0
--Doesn't return anything at all, becuase the CATCH block
--is empty. The error is silently ignored.
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
END CATCH
--Prevents an actual error from being raised,
--but returns the error as a rowset from a SELECT.
--This can be useful if you want to INSERT the errors
--into a logging table, for example.
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
--Similar to above, but PRINTs the message instead.
--Useful if you want to output something descriptive
--into the log of a job step, for example.
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()+' It occurred when trying to execute SELECT 1/0'
END CATCH
--Now use RAISERROR to change the severity of the error.
--Ordinarily it's severity 16, but let's say we wanted
--to downgrade it to an informational message.
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(max)=ERROR_MESSAGE()
RAISERROR (@ErrorMessage,10,1)
END CATCH
--Here the CATCH block is not reached, despite an error
--being encountered, because these sorts of errors are
--only trapped when they're at a lower level of execution
--than the TRY...CATCH block.
BEGIN TRY
SELECT * FROM TableDoesNotExist
END TRY
BEGIN CATCH
SELECT 'This code isn''t run!'
END CATCH
--With the same error, it is trapped now because
--the query's being called with EXECUTE, so is at a lower
--level of execution than the TRY...CATCH construct.
BEGIN TRY
EXECUTE ('SELECT * FROM TableDoesNotExist')
END TRY
BEGIN CATCH
SELECT 'Now this code is run!'
END CATCH
I hope this helps!
June 7, 2015 at 12:02 pm
Thnx Jacob. Appreciate that you spend your time on Sunday. I'm going thru the links which you provided.
One more question here. I mostly use my catch block in the procedures with the below code. But I have noticed that, the catch block will store the results, but if the job step fails, it should fail the entire JOB so that we can get alerts. But, I don't see that happening.
Is this because, I am not adding RAISeError in the catch block??
RAISERROR(@ErrorMessage , 16, 1).
Please suggest.
----------------------------------
The jobs are - copying local backup files to central server. Now, if incase the share path is not available(one of the examples), it throw fail the job and throw error so that we can get alerts. But, what I'm seeing is, the procedure fails with errorcode, but the job step does not fail. Instead it has the message as ..."error msg..., step succeeded.
How would I able to handle such situations, so that if the job step fails, it will force fail the job, so that we can observe later what's going on easily (as an alert will be thrown).
---------------------------------
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(4000);
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorNumber = Error_number(),
@ErrorSeverity = Error_severity(),
@ErrorState = Error_state(),
@ErrorLine = Error_line(),
@ErrorProcedure = Error_procedure();
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + Error_message();
SELECT @ErrorMessage AS [Error_Message];
SELECT @ErrorProcedure AS [Error_Procedure];
PRINT 'Error ' + CONVERT(VARCHAR(50), Error_number()) + ', Severity ' + CONVERT(VARCHAR(5), Error_severity()) + ', State ' + CONVERT(VARCHAR(5), Error_state()) + ', Procedure ' + Isnull(Error_procedure(), '-') + ', Line ' + CONVERT(VARCHAR(5), Error_line());
--PRINT Error_message();
SELECT Error_message();
END catch
SET NOCOUNT OFF
Thanks.
June 7, 2015 at 12:18 pm
No problem! I'm glad to help.
Yes, if that is the code within your CATCH block, the reason it's not failing the job is because no error is actually being raised.
You're trapping the error and PRINTing and SELECTing the contents of the message, but that will not cause the job to fail. For that you would need to raise an actual error using RAISERROR (or in 2012+, THROW, which Microsoft prefers you use).
Cheers!
June 7, 2015 at 12:28 pm
Now, THROW is an option available in SQL 2012 and after. So can't use that for lesser editions.
Can anyway, I can check that if my SQL edition is older than SQL 2012, I can use only "RaiseError" and for SQL 2012 and onwards, I can use throw in the catch block?
Also, Can you suggest instead of creating custom messages for each job step failure, (e.g, 5000 - bkp failure, 50001 - checkdb failkure, 5003 for rebuild index failure,), I can hold the error messages created at the catch block and can pass that to the Raiseerror function?
Which option out of the two look more promising to you??
Option 1:
EXEC sp_addmessage
@msgnum = 60000,
@severity = 16,
@msgtext =
N'Backup Failure.',
@lang = 'us_english';
Option 2:
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(4000);
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorNumber = Error_number(),
@ErrorSeverity = Error_severity(),
@ErrorState = Error_state(),
@ErrorLine = Error_line(),
@ErrorProcedure = Error_procedure();
RAISERROR(@ErrorMessage , 16, 1).
Again, do you use any specific script for Catch block, which you can use in multiple procedures in the catch block (sort of code re-usability)?
Please suggest...Thanks again.
Thanks.
June 7, 2015 at 12:38 pm
I wouldn't worry too much about using THROW, honestly. I thought it was worth mentioning that Microsoft recommends using THROW for new applications, but that isn't something I've bothered implementing yet.
I generally prefer just trapping the error, passing the message to a variable, and raising the error using that variable (sometimes with additional information, like the query that was running in the TRY block).
That's not to say it's necessarily better than using a user-defined message as in your Option 1, but I like the flexibility of being able to include things like the query text, value of variables and such in the message if I want to (to be fair, the two methods are not mutually exclusive).
Either approach (or both) should work, though.
Cheers!
June 7, 2015 at 1:47 pm
SQL-DBA-01 (6/7/2015)
Thnx Jacob. Appreciate that you spend your time on Sunday. I'm going thru the links which you provided.One more question here. I mostly use my catch block in the procedures with the below code. But I have noticed that, the catch block will store the results, but if the job step fails, it should fail the entire JOB so that we can get alerts. But, I don't see that happening.
Is this because, I am not adding RAISeError in the catch block??
RAISERROR(@ErrorMessage , 16, 1).
Please suggest.
----------------------------------
The jobs are - copying local backup files to central server. Now, if incase the share path is not available(one of the examples), it throw fail the job and throw error so that we can get alerts. But, what I'm seeing is, the procedure fails with errorcode, but the job step does not fail. Instead it has the message as ..."error msg..., step succeeded.
How would I able to handle such situations, so that if the job step fails, it will force fail the job, so that we can observe later what's going on easily (as an alert will be thrown).
---------------------------------
If you want the job to be failed, you must include either RAISERROR or THROW in the catch block. THROW is simpler unless you want to change the error detais, bcause you don't have to provide any parameters - without parameters it reraises the error that caused entry to the catch block.
Tom
June 7, 2015 at 9:55 pm
Thanks to everyone for your suggestion & feedback
Tomorrow I will do the required changes and will post you with the results.
Thanks.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy