November 21, 2010 at 3:25 am
I am using try/catch in my cursor so that the process will not if it fails on any one of the databases.But in the catch block i want to capture the database where it is failing and it captures only the ones on which the sp is not available and does not capture other errors..how do i capture each and every error in the catch block.Can someone please advice me?
November 21, 2010 at 3:36 am
Please post the code and your expected result/output.
And please explain what you mean by "each and every error".
November 21, 2010 at 4:11 am
Thanks for response. Lets say a procedure failed due to divide my zero error or if the procedure has to send an email and the profile is not available. In simple language i just need to find out on which database the procedure failed to execute along with the error. I am attaching the script, i would appreciate if you can advice me to make the necessary changes in the code. Thanks again.
November 21, 2010 at 5:03 am
Do have a TRY ... CATCH block inside your sproc [myprocedure], that would print any error message?
November 22, 2010 at 9:30 am
If the stored procedures being called include metadata from INFORMATION_SCHEMA.ROUTINES in the RAISERROR, they should return the desired results.
CREATE PROCEDURE usp_ErrorTest
AS
DECLARE @ProcName nvarchar(128);
DECLARE @RoutineCatalog varchar(128)
DECLARE @RoutineSchema varchar(128)
SET @ProcName = OBJECT_NAME(@@PROCID);
select @RoutineCatalog = R.ROUTINE_CATALOG, @RoutineSchema = r.ROUTINE_SCHEMA
from INFORMATION_SCHEMA.ROUTINES r
where r.ROUTINE_NAME = @ProcName;
BEGIN TRY
SELECT 1/0 -- SOME ERROR
END TRY
BEGIN CATTCH
declare @Error varchar(max) = error_message()
RAISERROR ('Stored procedure %s.%s.%s error: %s', 16,10, @RoutineCatalog,@RoutineSchema,@ProcName, @Error)
END CATCH
GO
November 22, 2010 at 12:21 pm
LutzM (11/21/2010)
Do have a TRY ... CATCH block inside your sproc [myprocedure], that would print any error message?
not sure what you mean
November 22, 2010 at 12:22 pm
janis.l.murphy (11/22/2010)
If the stored procedures being called include metadata from INFORMATION_SCHEMA.ROUTINES in the RAISERROR, they should return the desired results.
CREATE PROCEDURE usp_ErrorTest
AS
DECLARE @ProcName nvarchar(128);
DECLARE @RoutineCatalog varchar(128)
DECLARE @RoutineSchema varchar(128)
SET @ProcName = OBJECT_NAME(@@PROCID);
select @RoutineCatalog = R.ROUTINE_CATALOG, @RoutineSchema = r.ROUTINE_SCHEMA
from INFORMATION_SCHEMA.ROUTINES r
where r.ROUTINE_NAME = @ProcName;
BEGIN TRY
SELECT 1/0 -- SOME ERROR
END TRY
BEGIN CATTCH
declare @Error varchar(max) = error_message()
RAISERROR ('Stored procedure %s.%s.%s error: %s', 16,10, @RoutineCatalog,@RoutineSchema,@ProcName, @Error)
END CATCH
GO
thanks..but how can i embed this in my actual code which is attached to this posting?
November 22, 2010 at 12:48 pm
You would need to embed the logic within the procedure being called, not the procedure calling the procedure. So in the example you gave, each database's version of procedure dbo.[myprocedure] would have catch blocks and the logic I suggested so that the error message being passed back to your calling procedure includes the database name.
November 24, 2010 at 5:46 pm
Please do read Erland Somerskog's excellent articles on error handling in SQL server. Part 1 & Part 2. The articles were originally written for SQL 2000, then updated somewhat for SQL 2005. They still are among the best online resources on error handling in SQL server. Erland promised to add an article on SQL 2005 and later version's try-catch too. So far I've only seen this article. I have not seen a final version of it yet, but even this short introduction describes some of the main features already.
Reading these articles you will learn about the different types of errors that SQL server has, plus the different types of handling you need to deal with them. You will also learn that some errors can not be dealt with using either method.
In a nutshell: from 2005 on we have 2 types of error handling methodologies:
1 - check @@error after each and every statement and act accordingly or
2 - use try - catch blocks.
2 is the most powerful and should be prefered most of the times. I give an example of how I like to write error handling in my procedures. This pattern works for almost all situations. Note that I do not set xact_abort on as Erland suggests. Instead I explicitly set it to off: I've found that the catch block is not called in many cases with exact_abort on, causing many a transaction to go doomed. I think a doomed transaction is always bad news, so I like to prevent them as much as possible. When xact_abort is set to off, the catch block can be used to recover from a greater range of errors.
set xact_abort off; -- Indicate we do not want the engine to drop
-- out of our procedure when an error occurs.
-- As always with SQL server we can only give hints,
-- the engine may still decide to exit from our code
-- on very serious errors. There is no way we can
-- properly handle those errors, this is the best
-- we can do.
declare @nResult int;
begin tran trn; -- Start a transaction before the try block.
begin try
-- -- Do your error-prone task(s) here:
-- Example 1: For example try (properly handled, like most errors):
select 1/0;
----Example 2: Also constraint violations can be dealt with:
--declare @tbl table (
--n int not null,
--primary key (n));
--
--insert @tbl(n)
--select 1
--union all
--select 1;
-- -- Example 3: This will doom the transaction, i.e. when a nested transaction
-- -- was used, the parent's transaction is invalidated too.
-- declare @xml xml;
-- select @xml = N'<xml>with invalid xml format';
-- Last step in the try should be commit, you will only ever
-- get to here when no errors occured.
commit tran trn;
select @nResult = 0;
end try
begin catch
-- For debugging purposes, show the error information:
select error_number(), error_message(), error_procedure(), error_line(), xact_state();
-- We need to check for the "damage" the error did:
if xact_state() > 0
rollback tran trn; -- Transaction is still valid: we can safely
-- resume work (retry?) after returning
-- our connection to a predefined state,
-- i.e. just before we started the
-- transaction.
else if xact_state() < 0
rollback tran; -- When the transaction got doomed, we can only
-- do a rollback of an unnamed transaction. This
-- usually happens when an error occurs inside a
-- procedure call nested in our transaction
-- and the engine decides to break
-- else
-- -- The transaction got rolled back by the engine due to the
-- -- error, we can not do anything about that.
select @nResult = 50001; -- or any other return value you like.
end catch
return @nResult;
November 27, 2010 at 8:13 am
Thanks Janis and Rozema.. I will try this and let you k now
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply