November 2, 2009 at 5:27 pm
I have a code where I execute a transaction and I need to log an error if it occurs. I execute my transaction in TRY block, and catch an error in CATCH block.
Here is a sample of my code:
First, I create a table to insert some values in a transaction:
if object_id('t','u') is not null
drop table t
go
create table t
(c1 int)
go
-- Next, I create an error log table:
if object_id('error_log', 'u') is not null
drop table error_log
go
create table error_log
(err_num int,
err_message varchar(100)
)
go
--And here is my code where I try to log my error into error_log table:
declare
@error_number int,
@error_message varchar(100)
set xact_abort on
begin try
begin transaction
-- here I intentionally enter number that's larger than int:
insert into t values (67896789867);
insert into t values (5);
if xact_state() = 1 begin
commit transaction
end
end try
begin catch
select @error_number = error_number(),
@error_message = error_message()
print @error_number
print @error_message
insert into error_log
select @error_number, @error_message
rollback transaction
end catch
Upon execution of this batch I get an error:
"Msg 3930, Level 16, State 1, Line 21
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."
If I comment lines which insert into error_log table, I don't receive an error. But the problem is that I DO need to log errors.
Any suggestions please ?
November 2, 2009 at 5:57 pm
The rollback is affecting the insert into the error_log as well as the insert into t. At a minimum the rollback should be before the insert into error_log statement. Normally IF @@TRANCOUNT > 0 is wrapped around the rollback as well. This may not solve everything but it should help:
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
insert into error_log
select @error_number, @error_message
November 2, 2009 at 8:46 pm
Another thing you may consider. If you want to capture the data causing the error, you can insert that data into a table variable defined for that function, then you can roll back the transaction then use the data in the tale variable to update an error log or error audit table with the offending data for further analysis.
The reason the works is that table variables are not affected by transactions.
November 2, 2009 at 11:24 pm
This works fine.
declare
@error_number int,
@error_message varchar(100)
begin try
begin transaction
-- here I intentionally enter number that's larger than int:
insert into t values (67896789867);
insert into t values (5);
commit transaction
end try
begin catch
select @error_number = error_number(),
@error_message = error_message()
print @error_number
print @error_message
rollback transaction
insert into error_log
select @error_number, @error_message
end catch
SELECT * FROM T
SELECT * FROM ERROR_LOG
November 4, 2009 at 5:25 pm
Thanks, that's good solution, you moved insert ouside of transaction. This is what I needed.
November 5, 2009 at 11:38 am
Please be aware the ERROR_MESSAGE() function returns a nvarchar(4000). So you will run the risk of truncating some of the messages using the variable declared as varchar(100).
November 6, 2009 at 4:22 am
Adam Gojdas (11/5/2009)
Please be aware the ERROR_MESSAGE() function returns a nvarchar(4000). So you will run the risk of truncating some of the messages using the variable declared as varchar(100).
It's NVARCHAR(2048) on my SQL Servers :laugh:
November 6, 2009 at 4:24 am
SQL Guy-482125 (11/4/2009)
Thanks, that's good solution, you moved insert ouside of transaction. This is what I needed.
You might like to change the ROLLBACK TRANSACTION to:
IF XACT_STATE() != 0 BEGIN ROLLBACK TRANSACTION END;
The ROLLBACK TRANSACTION on its own would generate its own error if the original error terminated the active transaction. In that case, no error details would be logged, which is probably not what you want.
edit: changed <> to != in the code block due to dodgy site formatting :rolleyes:
November 7, 2009 at 9:43 am
Paul White (11/6/2009)
Adam Gojdas (11/5/2009)
Please be aware the ERROR_MESSAGE() function returns a nvarchar(4000). So you will run the risk of truncating some of the messages using the variable declared as varchar(100).It's NVARCHAR(2048) on my SQL Servers :laugh:
Thanks for pointing that out Paul. Seems there is a discrepancy. The BOL I have installed for 2005 states: nvarchar(4000). But after searching the msdn website's BOL I see it lists both 2005 and 2008: nvarchar(2048).
I have to wonder what's up with that?
November 7, 2009 at 9:52 am
Paul White (11/6/2009)
SQL Guy-482125 (11/4/2009)
Thanks, that's good solution, you moved insert ouside of transaction. This is what I needed.You might like to change the ROLLBACK TRANSACTION to:
IF XACT_STATE() != 0 BEGIN ROLLBACK TRANSACTION END;The ROLLBACK TRANSACTION on its own would generate its own error if the original error terminated the active transaction. In that case, no error details would be logged, which is probably not what you want.
edit: changed <> to != in the code block due to dodgy site formatting :rolleyes:
I also like to check transactions with that method in my stored procedures. But I sometimes find when running scripts to test code with transactions I have to do the following to rollback all since I always have my test scripts in transactions for the testing.
WHILE (XACT_STATE() <> 0) BEGIN
ROLLBACK TRANSACTION;
END;
November 7, 2009 at 9:48 pm
Adam Gojdas (11/7/2009)
WHILE (XACT_STATE() <> 0) BEGIN
ROLLBACK TRANSACTION;
END;
Hey Adam,
Not trying to be 'smart' here...but ROLLBACK always rolls back completely, through any number of 'nested' transactions.
You might be thinking of the way COMMIT TRANSACTION works, or possibly be confusing ROLLBACK with ROLLBACK to a savepoint.
Paul
November 8, 2009 at 6:17 am
Paul White (11/7/2009)
Adam Gojdas (11/7/2009)
WHILE (XACT_STATE() <> 0) BEGIN
ROLLBACK TRANSACTION;
END;
Hey Adam,
Not trying to be 'smart' here...but ROLLBACK always rolls back completely, through any number of 'nested' transactions.
You might be thinking of the way COMMIT TRANSACTION works, or possibly be confusing ROLLBACK with ROLLBACK to a savepoint.
Paul
Yes, I know what you are talking about and had always thought that same exact thing. But for some reason with my scripts and the the way the code was when I would be running and rerunning from SSMS I would get a message that there was still a transaction left. I then put it into the while loop and didn't have an issue. I know it sounds pretty odd and probably should have stated this with my initial reply. I will have to see if I can gather an example of this situation...or else it may have all been in my head.:w00t:
November 8, 2009 at 1:28 pm
Makes you wonder if the connection had IMPLICIT_TRANSACTIONS set on. Well, never mind, I'm sure there was a non-paranormal explanation 🙂
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply