Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, December 16, 2005 9:05 PM | 1 vote
I am trying to use the uspLogError and uspPrintError stored procedures found in AdventureWorks and I have a problem. I have a stored procedure called Is_uspProcessLeads that processes some customer information that is in xml format. Part of the process is to check to make sure that the xml is well formed and if it is validated. Each step along the way, Is_uspProcessLeads updates a status field to indicate how far along in the process the xml file got, and if there is an error, to capture that error in a log file. The error handling process uses a try..catch logic.
Is_uspProcessLeads does it's processing against a table called [Is.Leads] which contains the following fields...
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlValidated typed Xml data type with a related xsd file
IsLead_LeadStatus_Id Status code field
So here's the problem. If I run Is_uspProcessLeads manually and I get an error (like not well formed) then it blows up as I would expect and logs the error correctly. But what I want to do is run Is_uspProcessLeads from the Insert trigger of [Is.Leads]. When I try that, I get an unexpected error in uspLogError when it tries to insert error information in the Error log table, therefore preventing me from logging my errors.
Here is the errors I get
V
Error: **3930
**Severity: 16
State: 1
Procedure: IsuspLogError
Line: 43
ProcessLocationMsg: Confirm that the raw xml is well formed
Lead_Id: 20
ERROR_MESSAGE...
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
^
Msg 3616, Level 16, State 1, Line 12
Transaction doomed in trigger. Batch has been aborted.
Got any Ideas why this is happening?
I searched for the 3616 error message, and got nothing (I do however like the message about being doomed). It looks like SQL Server is treating the trigger event as a transaction
FYI: The error message inside the dashed lines comes from uspPrintError, which is executed inside the catch block of uspLogError
Monday, December 19, 2005 8:22 PM ✅Answered | 1 vote
Since you are calling the SP inside the trigger all errors needs to be rolled back before you performing any writes. So in the Is_uspProcessLeads CATCH block you are missing the XACT_STATE() check and rollback. See below lines:
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK add this line here:
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @IsLead_Id, @ProcessLocationMsg;
END CATCH;
Or you may just need to rollback the transaction without checking for XACT_STATE().
Saturday, December 17, 2005 1:12 AM | 1 vote
This error occurs when you try to make any modifications after a transaction that needs to be rolled back. You can detect this using XACT_STATE function in the CATCH block. If the value is -1 it indicates that the transaction cannot be committed and you need to issue a rollback so subsequent transactions cannot proceed. If you can post a simple repro of the problem it will be easier to suggest the modification.
Monday, December 19, 2005 3:19 PM
The stored procedured Is_uspLogError already checks XACT_STATE Function, see below. Below are the relative three stored procs.
1) Is_trgLead_InsertTrigger is the trigger,
2) Is_uspProcessLeads has the try catch block of code and
3) Is_uspLogError logs the error
ALTER TRIGGER [Is_trgLead_InsertTrigger]
ON [dbo].[Is.Lead]
AFTER INSERT --FOR INSERT ?
AS
BEGIN
SET NOCOUNT ON; -- prevent extra result sets from interfering with SELECT statements.
-- =============================================
-- Declaration
-- =============================================
DECLARE @ReturnCode int
DECLARE @Lead_Id int
DECLARE @FTP_Id int
DECLARE @ProcessLocationMsg varchar(255)
Set @ProcessLocationMsg = 'Initialization'
If (select Skip_IsLead_Trigger from dbo.[Is.Environment]) = 1
Return
SELECT
@Lead_Id = IsLead_Id,
@FTP_Id = IsLead_FTP_Id
from Inserted
Set @ProcessLocationMsg = 'Call sproc IsProcessLeads'
EXEC @ReturnCode = [Is_uspProcessLeads]
@IsLead_Id = @Lead_Id,
@IsFTP_Id = @FTP_Id
END
ALTER PROC [dbo].[Is_uspProcessLeads]
@IsLead_Id int,
@IsFTP_Id int
AS
/*
USE [Integration]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[IsProcessLeads]
@IsLead_Id = 1,
@IsFTP_Id = 1
SELECT 'Return Value' = @return_value
GO
select * from dbo.[Is.ErrorLog]
Go
*/
BEGIN
--Declarations.
DECLARE @FTP_TYPE_ID_AHG int -- American Home Guides
DECLARE @FTP_TYPE_ID_BHI int -- Builder Homesite Inc
DECLARE @FTP_TYPE_ID_HB int -- Homebuilder
DECLARE @FTP_TYPE_ID_INEST int -- iNest
DECLARE @FTP_TYPE_ID_RYLAND int -- Ryland.Com
DECLARE @LEAD_STATUS_ID_START int
DECLARE @LEAD_STATUS_ID_Completed int
DECLARE @LEAD_STATUS_ID_XmlWellFormed int
DECLARE @LEAD_STATUS_ID_XmlValid int
--SET @LEAD_STATUS_ID_RaisedError int
DECLARE @LEAD_STATUS_ID_EmailedCLEAR int
DECLARE @LEAD_STATUS_ID_EmailedCustomer int
Declare @CLEAREmail nvarchar(255)
Declare @CLEAREmailMsg nvarchar(255)
Declare @AutoResponseEmail nvarchar(255)
Declare @AutoResponseEmailMsg nvarchar(255)
DECLARE @ReturnCode int
Declare @ProcessLocationMsg nvarchar(255)
--Initialization.
SET @FTP_TYPE_ID_AHG = 1
SET @FTP_TYPE_ID_BHI = 4
SET @FTP_TYPE_ID_HB = 6
SET @FTP_TYPE_ID_INEST = 7
SET @FTP_TYPE_ID_RYLAND = 0
SET @LEAD_STATUS_ID_START = 1
SET @LEAD_STATUS_ID_Completed = 2
SET @LEAD_STATUS_ID_XmlWellFormed = 3
SET @LEAD_STATUS_ID_XmlValid = 4
--SET @LEAD_STATUS_ID_RaisedError
SET @LEAD_STATUS_ID_EmailedCLEAR = 6
SET @LEAD_STATUS_ID_EmailedCustomer = 7
-- Confirm that the raw xml is well formed and valid, by updating the specific xml field
-- which is determined by the FTP_TYPE_ID, with the Raw xml
BEGIN TRY
Set @ProcessLocationMsg = 'Confirm that the raw xml is well formed'
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @IsLead_Id
Set @ProcessLocationMsg = 'Confirm that the well formed xml is valid'
IF @IsFTP_Id = @FTP_TYPE_ID_AHG
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlWellFormed
WHERE IsLead_Id = @IsLead_Id
Else IF @IsFTP_Id = @FTP_TYPE_ID_BHI
UPDATE [Is.Lead]
SET IsLead_XmlBHI = IsLead_XmlWellFormed
WHERE IsLead_Id = @IsLead_Id
Else IF @IsFTP_Id = @FTP_TYPE_ID_HB
UPDATE [Is.Lead]
SET IsLead_XmlHB = IsLead_XmlWellFormed
WHERE IsLead_Id = @IsLead_Id
Else IF @IsFTP_Id = @FTP_TYPE_ID_INest
UPDATE [Is.Lead]
SET IsLead_XmliNest = IsLead_XmlWellFormed
WHERE IsLead_Id = @IsLead_Id
-- Update status to show the xml is valid
Set @ProcessLocationMsg = 'Update status to show the xml is valid'
UPDATE [Is.Lead]
SET IsLead_LeadStatus_Id = @LEAD_STATUS_ID_XmlValid
WHERE IsLead_Id = @IsLead_Id
-- Update status to show the xml is valid
Set @ProcessLocationMsg = 'Get the count of leads'
UPDATE [Is.Lead]
SET IsLead_CountOfLeads = ([dbo].[Is_ufnCountOfLeads](@IsLead_Id, @IsFTP_Id))
WHERE IsLead_Id = @IsLead_Id
Proc_Exit:
END TRY
BEGIN CATCH
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @IsLead_Id, @ProcessLocationMsg;
END CATCH;
RETURN <--@@ERROR>
End
/*
IsuspLogError logs error information in the ErrorLog table about the
error that caused execution to jump to the CATCH block of a
TRY...CATCH construct. This should be executed from within the scope
of a CATCH block otherwise it will return without inserting error
information.
*/
ALTER PROCEDURE [dbo].[Is_uspLogError]
@Lead_Id int,
@ProcessLocationMsg varchar(255) = '',
@ErrorLogID int = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by IsuspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;
-- Output parameter value of 0 indicates that error
-- information was not logged
SET @ErrorLogID = 0;
BEGIN TRY
-- Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;
-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing IsuspLogError in order to successfully log error information.';
RETURN;
END
PRINT 'Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]'
INSERT [dbo].[Is.ErrorLog]
(
[IsErrorLog_UserName],
[IsErrorLog_Number],
[IsErrorLog_Severity],
[IsErrorLog_State],
[IsErrorLog_Procedure],
[IsErrorLog_Line],
[IsErrorLog_ProcessLocationMsg],
[IsErrorLog_Message],
[IsErrorLog_Lead_Id]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
@ProcessLocationMsg,
ERROR_MESSAGE(),
@Lead_Id);
-- Pass back the ErrorLogID of the row inserted
SET @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT 'Inside Isusp_LogError!Begin Catch ';
EXECUTE [dbo].[Is_uspPrintError] @Lead_Id, @ProcessLocationMsg;
RETURN -1;
END CATCH
END;
Monday, December 19, 2005 3:45 PM
FYI: I did some more testing to see what the value of XACT_STATE() is, and it doesn't change, it's always zero. Inside Is_uspLogError, I did a print before the Insert statement, and inside the Catch statement, and it was always 0.
Error inside Is_uspProcessLeads; calling Is_uspLogError
1 XACT_STATE(): 0
Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]
2 XACT_STATE(): 0
Inside Isusp_LogError!Begin Catch
Tuesday, December 20, 2005 5:36 PM
Thanks for the response,
The problem with your solution is that I don't want to roll back the data that gets inserted in table [Is.Lead]. I want it there, so I can report what went wrong. If do a rollback, I will have an entry in the [Is.ErrorLog] table but nothing in [Is.Lead].
I guess my design is flawed, do you have any suggestions?
I think I need to remove the insert trigger, and have Is_uspProcessLeads executed some other way. I suppose I could do this by putting Is_uspProcessLeads on the scheduler, or do something with Services Broker.
Thanks
Tuesday, December 20, 2005 10:36 PM | 1 vote
The issue is that when errors happen inside trigger most of them are of the transaction abort nature and the only recourse is to rollback the transaction. Since there is no support for autonomous transactions in SQL Server there is really no way to do what you are trying. Best is to move this logic to a stored procedure so that in case of an error you can catch it appropriately and take action. Note that this still depends on the setting of XACT_ABORT.
Thursday, January 18, 2007 4:50 PM
Im not seeing it, I need help. ANY help would be greatly appreciated
How can I get the try block in a trigger to stop throwing errors that im trying to skip over?
here is my example:
IF (OBJECT_ID ('cwtest') IS NOT NULL) drop table cwtest
go
create table cwtest (test varchar(20))
go
IF (OBJECT_ID ('cwtest_tr_IUD') IS NOT NULL) DROP trigger cwtest_tr_IUD
go
create trigger cwtest_tr_IUD on cwtest
for insert, update, delete
as
DECLARE
@dml char(1)
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK add this line here:
END CATCH
select 'why is the query completed with errors? Is this a bug?,This works fine anywhere else!'
end
go
insert into cwtest values('dd')
go
-- here is the base test works wonderfully!
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK add this line here:
END CATCH;
select 'error is not returned,query executed successfully, whats up? '
end
go
and the output :
(0 row(s) affected)
why is the query completed with errors?
(1 row(s) affected)
Msg 3616, Level 16, State 1, Line 2
Transaction doomed in trigger. Batch has been aborted.
(0 row(s) affected)
error is not returned,query executed successfully, whats up?
(1 row(s) affected)