November 20, 2009 at 9:15 am
1)I created a table to log errors. To write into the table I wrote a procedure:
============================================
ALTER PROCEDURE [dbo].[sp_SqlInsertError]
AS
declare
@ErrorNumber int,
@ErrorSeverity int,
@ErrorState int,
@ErrorProcedure nvarchar(126) ,
@Error_Line int,
@ErrorMessage nvarchar(2048)
SELECT
@ErrorNumber=ERROR_NUMBER(),
@ErrorSeverity=ERROR_SEVERITY(),
@ErrorState=ERROR_STATE(),
@ErrorProcedure=ERROR_PROCEDURE(),
@Error_Line =ERROR_LINE(),
@ErrorMessage=ERROR_MESSAGE()
Insert Into ErrorLog
( Location, Module, ProcedureInvolved, UserID, DateTimeOccured, ErrorNumber, ErrorDescription, Severity, Comments
)
Values
(@@SERVERNAME, 'SQL Server', @ErrorProcedure, system_user,
GETDATE(), @ErrorNumber, @ErrorMessage, @ErrorSeverity,
'Error Line: ' + CAST( @Error_Line as nvarchar(10)) + ', Error State: ' + CAST(@ErrorState as nvarchar(15))
)
=============================================================
2) It worked OK in codes like that:
==============================
ALTER Proc [dbo].[sp_ErrorTest]
as
begin try
select i=1/0
end try
begin catch
exec sp_SqlInsertError
end catch
==============================================================
3) I included this in the following procedure:
==========================================================
ALTER PROCEDURE [dbo].[sp_InsertKeyIssues]
@KeyIssuesText nvarchar (250),
@ClinicalReviewID int
AS
SET NOCOUNT ON;
BEGIN Try
Insert into tblKeyIssue
(KeyIssuesText ,ClinicalReviewID)
Values
(@KeyIssuesText ,@ClinicalReviewID)
return @@rowcount
Print cast(@@rowcount as nchar(1))
END Try
begin catch
exec sp_SqlInsertError
end catch
===========================
I run this procedure generating error:
a) exec sp_InsertKeyIssues 'test', 'abc'
I got a local error message (cannot convert int to nvarchar) - and NOTHING WAS WRITTEN INTO THE ERROR LOG.
b) even more surprising that this code DID NOT GENERATE ERROR at all.
exec sp_InsertKeyIssues 'test', '123'
WHAT AM I DOING WRONG?
November 23, 2009 at 7:17 am
a) exec sp_InsertKeyIssues 'test', 'abc'
I got a local error message (cannot convert int to nvarchar) - and NOTHING WAS WRITTEN INTO THE ERROR LOG.
What was the error number and text, please?
b) even more surprising that this code DID NOT GENERATE ERROR at all.
exec sp_InsertKeyIssues 'test', '123'
If this code doesn't generate an error, then the problem doesn't lie with the try/catch block. What error are you expecting it to generate?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2009 at 8:03 am
Note that the second parameter in the main procedure declared as int .
In the first run I put quoted 'abc' - and it returned a message 'cannot convert nvarchar to int'
In the second run I used quoted 123 ('123') and I expected that if I surround a number with quotes - it is a string (nvarchar) - but it looks that the engine made an implicite conversion of string into number (int) type.
My code did not return the error number, however if this is important I can change the code and print ERROR_NUMBER.
This was my first take on Try - Catch syntax I used Raise Error before.
Thanx
November 23, 2009 at 8:38 am
pretty sure the issue is that the error is raised BEFORE the procedure is executed.
like you identified, when the compiler tries to send 'asbc' to an integer datatype, it raises an error and exists...all before your code gets executed.
if you changed the datatype to varchar, and tried to cast it to integer inside of the procedure, then you could see it in your try-catch block.
Lowell
November 23, 2009 at 9:09 am
Lowell,
I changed the procedure:
=============
ALTER PROCEDURE [dbo].[sp_InsertKeyIssues]
@KeyIssuesText nvarchar (250),
@ClinicalReviewID nvarchar(10)
AS
SET NOCOUNT ON;
BEGIN Try
Insert into tblKeyIssue
(KeyIssuesText ,ClinicalReviewID)
Values
(@KeyIssuesText ,cast(@ClinicalReviewID as int))
return @@rowcount
Print cast(@@rowcount as nchar(1))
END Try
begin catch
exec sp_SqlInsertError
end catch
=============
Then tried to generate an error again:
-- exec sp_InsertKeyIssues 'test', '11232009'
-- exec sp_InsertKeyIssues 'test', 11232009
In both examples a new record was inserted and no error was generated, e.g. sp_SqlInsertError was not called
November 23, 2009 at 9:39 am
this is how i thin k it needs to be written...somewhere near the end you are calling your audit proc, but not passing the values...i think that's the issue.
also, your proc sp_SqlInsertError should not be re-selecting any errors, it should only log whatever was passed to it....because the errors occur outside of the procedures scope, they would be empty i think? because the SELECT would reset the errors?
i didn't have the error table to acutally be able to test this, but i grabbed my try catch model and edited yours to match:
--####################################################################
ALTER PROCEDURE [dbo].[sp_InsertKeyIssues]
@KeyIssuesText nvarchar (250),
@ClinicalReviewID nvarchar(10)
AS
SET NOCOUNT ON;
BEGIN Try
Insert into tblKeyIssue
(KeyIssuesText ,ClinicalReviewID)
Values
(@KeyIssuesText ,cast(@ClinicalReviewID as int))
return @@rowcount
Print cast(@@rowcount as nchar(1))
END Try
begin catch
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT,
@ErrorProcedure nvarchar(200),
@Error_Line int
--if you don't assign them, they are nulls
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
SET @Error_Line = ERROR_LINE()
SET @ErrorProcedure = object_name(@@procid)
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,
@ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
exec sp_SqlInsertError @ErrorNumber,@ErrorSeverity,@ErrorState,@Error_Line,@ErrorMessage
end catch
Lowell
November 23, 2009 at 9:59 am
Thanks. I'll try this one. But look, if I run this procedure:
=================
ALTER Proc [dbo].[sp_ErrorTest]
as
begin try
select 1/0
end try
begin catch
exec sp_SqlInsertError
end catch
==========================
the sp_SqlInsertError works OK, e.g. inserts a row into ErrorLog table.
November 23, 2009 at 1:46 pm
Some classes of errors pop the error outside of the current context and end up creating the error in the outer context .
Just for testing purposes - try setting up a TRY/CATCH around the stored proc call, and see if that catches the error.
As in - use your original stored procedure, and then call it using
BEGIN TRY
exec sp_ErrorTest
END TRY
BEGIN CATCH
print 'error'
end catch
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 23, 2009 at 2:04 pm
Thank you Matt.
This code worked perfectly well: the error record was written into the Error Log table, and catch print message did not show up. So ... what's now?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply