May 27, 2008 at 2:50 pm
Hello All,
We have a SQL 2005 database application that uses nested transactions. For example, a procedure will open a transaction, and call another procedure within that transaction. If an error is trapped via a TRY/CATCH block, the transactions are rolled back, and an error is logged. For example:
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
EXECUTE [dbo].[spErrorLog]
@error_message= @ErrorInfo, @error_severity = @error_severity
, @error_number= @error_number, @error_procedure = @error_procedure, @userid = @userid
, @ErrorLogID= @ErrorLogID OUTPUT, @ErrorXML = @ErrorXML OUTPUT
, @ErrorStatus= @ErrorStatus OUTPUT, @ErrorInfo = @ErrorInfo OUTPUT ;
RETURN;[/size]T
May 27, 2008 at 2:51 pm
sorry, i wasn't done yet! I will finish post in next post!
May 27, 2008 at 2:55 pm
So, while the rollback strategy works just fine, if the trancount is 2-3 or more, we get the following error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
DBCC CHECKDB yields nothing of interest, nor do the SQL logs. I don't seem to be able to trap for this severe error, either.
As I noted, the rollback is working as expected, everything looks fine, except this error. Does anyone have any suggestions?
Many thanks,
Kevin
May 27, 2008 at 3:02 pm
Are there anything different about the transactions? If you take one that works, add another nested transaction, and then try it, does it still abort?
Seems strange. Can you provide some code samples that aren't too long? Can you duplicate this with a simple example?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2008 at 3:56 pm
Thanks Steve for your reply.
I hope the following information answers your questions:
We ingest an XML string, and depending on parsed values, pass the string to one of several child procedure for further parsing and processing.
We use xpath to parse an XML string. If the XML parses correctly, then all the transactions complete as expected.
But I can induce the error at will, by inputting unexpected XML. When that happens, the parse fails, an error is thrown, and the rollback occurs.
Here, for example, is a snippen of XML malformed ("SPIKE_THIS_CHANGE") such that the error is induced:
...
...
This XML is sent to the correct procedure to process it, but when the final parse fails, an error is trapped, such as:
IF @Name IS NULL OR LEN(@Name) = 0
BEGIN
BEGIN TRY
SELECT @ErrorInfo= N'Error in spChange: @Name is NULL or an empty string.'; ...
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
EXECUTE [dbo].[spErrorLog]
@error_message= @ErrorInfo...;
RETURN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
END;
The complete set of transactions rolls back, as expected. But the "Severe Error" is returned as well. The database is behaving just as we want it, except for this "Severe Error".
Kevin
May 27, 2008 at 3:58 pm
i'm sorry, i have to learn to hit the preview button. That doesn't look right at all. Let me try again.
May 27, 2008 at 4:05 pm
We ingest an XML string, and depending on parsed values, pass the string to one of several child procedure for further parsing and processing.
We use xpath to parse an XML string. If the XML parses correctly, then all the transactions complete as expected.
But I can induce the error at will, by inputting unexpected XML. When that happens, the parse fails, an error is thrown, and the rollback occurs.
Here, for example, is a snippet of XML (with "{}" replacing "<>" so that the XML will show up below) malformed ("SPIKE_THIS_CHANGE") such that the error is induced:
...
{schChange changeNumber="1" objectType="Tb" changeType="Add"}
{tblChange SPIKE_THIS_CHANGE_Name="NewT1"}
...
This XML is sent to the correct procedure to process it, but when the final parse fails, an error is trapped, such as:
IF @Name IS NULL OR LEN(@Name) = 0
BEGIN
BEGIN TRY
SELECT @ErrorInfo = N'Error in spChange: @Name is NULL or an empty string.'; ...
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
EXECUTE [dbo].[spErrorLog]
@error_message = @ErrorInfo...;
RETURN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
END;
The complete set of transactions rolls back, as expected. But the "Severe Error" is returned as well. The database is behaving just as we want it, except for this "Severe Error".
May 27, 2008 at 4:27 pm
Very strange. I think you might want to investigate naming the transactions, being able to only rollback the nested one. OR, track that an error has occurred, and issue one rollback, either at the end, or in the middle and then bypassing all other code. I suspect code further down the line is giving you the severe error.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2008 at 4:35 pm
If a failure occurs, we do need to roll back the entire batch of changes to the last known good state. Your suggestion regarding named transactions is a good one; I did try using them earlier, with no luck. However I should try again. Thanks for helping me think about this problem.
Sincerely,
Kevin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply