August 27, 2010 at 12:04 pm
Okay, I'm going to give generic pseudo-code, so if this is a problem, lemme know. And I'm just checking to see if my logic is correct.
We have a Try block in the ELSE part of an IF...ELSE statement, with the CATCH right after. In the block, we had a problem where someone tried to insert into a non-existent column name. The code kept failing at execution (an automated job) and NOT rolling back the way it should.
Create Procedure spMySillyProc (@Varme)
AS
IF @Varme = 1
PRINT 'Tag, you are it.'
ELSE
BEGIN TRY
...lots of code...
INSERT INTO dbo.MyTable (GoodCol1, GoodCol2, BadCol)
(SELECT 1, 2, 4);
...lots more code...
END TRY
BEGIN CATCH
...Rollback & raiseerror code.
END CATCH
We were wondering if the reason our Catch wasn't catching & rolling back the transaction was because of this line from BOL:
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
Compile errors, such as syntax errors, that prevent a batch from running.
EDIT: The thought, which I failed to articulate, is that because the syntax error is in the ELSE clause, like the TRY block is, that these two things are "on the same level."
We are testing this theory, as I write this, but I wanted to see if anyone had any thoughts we hadn't considered yet.
August 27, 2010 at 3:15 pm
Your pseudo-code doesn't have an explicit transaction defined and you didn't mention whether you had changed your transaction management mode, so I'm assuming that it's using the default which is autocommit. If that is the case in your real code, that is the source of your problem. Each of your transactions is being autocommitted or auto-rolled back, so that when you issue the explicit rollback, there are no open transactions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 27, 2010 at 3:39 pm
Brandie, you could just change your code to:
Create Procedure spMySillyProc (@Varme)
AS
BEGIN TRY
IF @Varme = 1
PRINT 'Tag, you are it.'
ELSE
BEGIN
...lots of code...
INSERT INTO dbo.MyTable (GoodCol1, GoodCol2, BadCol)
(SELECT 1, 2, 4);
...lots more code...
END
END TRY
BEGIN CATCH
...Rollback & raiseerror code.
END CATCH
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 5:08 am
Wayne, It's not my code. It's someone else's. Yes, changing the code to that was one of our thoughts, but we're not sure if that will fix the issue. That's why I'm asking the question, to see if someone actually knows the answer.
Drew, the code author had no specific transactions within the TRY block. He's treating the TRY as all one transaction. It should be an All or Nothing proposition. That's why it's frustrating him that it errors out in the middle without going to the CATCH block and rolling back.
EDIT: What's "transaction management mode" mean? I haven't heard that one before and don't see it in BOL.
August 30, 2010 at 11:07 am
Brandie Tarvin (8/30/2010)
Drew, the code author had no specific transactions within the TRY block. He's treating the TRY as all one transaction. It should be an All or Nothing proposition. That's why it's frustrating him that it errors out in the middle without going to the CATCH block and rolling back.
The problem is that BEGIN TRY does NOT start a new transaction. It is not directly related to managing transactions. You can run the following code to check for yourself.
BEGIN TRY
SELECT @@TRANCOUNT
END TRY
BEGIN CATCH
END CATCH
It IS going to the CATCH block. The problem is that there is nothing for the CATCH block to do, because there are no open transactions. You can see this by putting a PRINT statement at the beginning of your catch block.
EDIT: What's "transaction management mode" mean? I haven't heard that one before and don't see it in BOL.
You can find it here: http://msdn.microsoft.com/en-us/library/ms175523%28SQL.90%29.aspx
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 30, 2010 at 12:39 pm
Actually - compile errors (which I believe is what you'd get if you try to insert into a column that doesn't exist) occur at the same level as the stored proc, so the INNER try...catch won't "catch" anything, since the entire context it was operating in dumps.
In that case you'd need an OUTER try...catch around the stored proc call.
BEGIN TRY
Exec myproc
END TRY
BEGIN CATCH
print 'oops, something stinks in this execution'
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?
August 31, 2010 at 6:30 am
Matt Miller (#4) (8/30/2010)
Actually - compile errors (which I believe is what you'd get if you try to insert into a column that doesn't exist)
Actually, that's a runtime error. SQL uses optimistic name resolution, so the column doesn't need to exist in order to compile the code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 13, 2010 at 5:26 am
Sorry it took so long to get back to this thread.
There is code within the TRY that should be creating transactions. But you're saying that the runtime error is happening before this point?
Am I understanding that correctly?
Thanks for the link. I'll read that now.
September 13, 2010 at 5:38 am
Okay, wait. I just caught up with something you said about "implicit transactions."
No, there actually was a BEING TRANSACTION and COMMIT TRANSACTION wrapped around the "lots of code" part of my pseudocode. And you're absolutely right that I should have written it into the pseudocode, but forgot.
Here's a "better version":
Create Procedure spMySillyProc (@Varme)
AS
IF @Varme = 1
PRINT 'Tag, you are it.'
ELSE
BEGIN TRY
...Drop any temp tables...
BEGIN TRANSACTION InsUpd;
...lots of code...
INSERT INTO dbo.MyTable (GoodCol1, GoodCol2, BadCol)
(SELECT 1, 2, 4);
...lots more code...
COMMIT TRANSACTION InsUpd;
END TRY
BEGIN CATCH
...Rollback & raiseerror code.
END CATCH
This is what the developer actually had. We finally figured out that he had an INSERT within the Transaction where he was trying to use a wrong column name in the INSERT field list.
So, looking back over your posts, are you saying the Transaction closed due to a runtime error and therefore, the Catch was reached but didn't work because of the runtime error?
September 13, 2010 at 6:01 am
Hi Brandie,
I think you answered your own question in your initial post 🙂
Consider this code
Drop Procedure spMySillyProc
go
Create Procedure spMySillyProc
AS
BEGIN TRY
print 'running'
BEGIN TRANSACTION InsUpd;
insert into mysilltablethatwontexist(col1) values (1);
print 'After insert'
Commit TRANSACTION InsUpd;
END TRY
BEGIN CATCH
rollback
END CATCH
go
print @@TRANCOUNT -- Look ma , no transaction
exec spMySillyProc
print @@error
if(@@TRANCOUNT >0) begin
print 'transopen'
rollback
end
The table 'mysilltablethatwontexist' doesent exist , to the procedure is stopped in its tracks and control is returned to the caller.
September 14, 2010 at 4:25 am
Did I? I'm trying to figure out *why* the code stopped. Maybe I'm just being dense here. If so, I apologize.
[EDIT]: I need to stop posting so early in the morning. Brain not functioning. So, if I understand this, no matter what we do, if the devs mess up with column names or table names that don't exist, there's no way to roll back changes that might have happened right before that point?
[Cont. EDIT] Except, I guess the better question is, *did* the changes actually occur or did the error get caught by SQL Server before any of the INSERTS previous to the bad one? I've actually seen SQL do both (not process any changes due to a later error and sometimes process changes regardless of later error).
September 14, 2010 at 6:09 am
Bad bad bad morning when I can't even articulate the simplest of thoughts.
Our problem with this was that we were indeed getting inserts-previous to the bad insert-sticking data into tables. Granted, we don't ever want an error like this again (bad column name), but it's annoying that when we did have the error, the rollback didn't work at all.
We've got the code corrected. All I'm trying to do at this point is play catchup and find a method to prevent this scenario from occurring again. If anyone has any thoughts, I'd greatly appreciate it.
Thank you to all who have posted before. I appreciate the advice.
September 14, 2010 at 6:19 am
Well, yes you can roll them back just not in the proc that failed. You will have to check the @@Error status in the caller routine , as the proc that errored is no longer running. Checking @@ERROR after every Stored proc call is good practice anyway.
Also another option is XACT ABORT (as shown below), i personally prefer to keep control , but i see no reason why you might want to commit a half complete transaction.
As for your other question , re sometimes the inserts happen sometimes not, i guess this has to with when the statement is compiled. You cant create a procedure that has an reference to a missing column but you can if the table is missing. So your issue may be that the plan was compiled ok but the column now no longer exists....
In any case , to my mind, its a moot point , an error has occurred and the whole transaction needs rolling back.
Drop Procedure spMySillyProc
go
Create Procedure spMySillyProc
AS
BEGIN TRY
print 'running'
BEGIN TRANSACTION InsUpd;
insert into mysilltablethatwontexist(col1) values (1);
print 'After insert'
Commit TRANSACTION InsUpd;
END TRY
BEGIN CATCH
rollback
END CATCH
go
set xact_abort off
print @@TRANCOUNT -- Look ma , no transaction
exec spMySillyProc
print @@error
if(@@TRANCOUNT >0) begin
print 'transopen'
rollback
end
go
set xact_abort on
print @@TRANCOUNT -- Look ma , no transaction
exec spMySillyProc
print @@error
if(@@TRANCOUNT >0) begin
print 'transopen'
rollback
end
September 14, 2010 at 7:35 am
I tried a slightly different tack, thinking that perhaps the conditional was screwed by the lack of a BEGIN-END block around the TRY-CATCH:
DECLARE @Splitter BIT
SET @Splitter = 1
IF @Splitter = 1
PRINT 'IF'
ELSE
--BEGIN
BEGIN TRY
PRINT 'SECOND LINE'
PRINT 'THIRD LINE'
SELECT GETDATE(), 1/0
END TRY
BEGIN CATCH
PRINT 'CAUGHT DIVIDE BY ZERO ERROR'
END CATCH
--END
PRINT 'AFTER'
- but it doesn't make any difference.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2010 at 11:48 am
Thanks, guys. Now I know what I read in BOL had nothing to do with why the proc reacted the way it did.
Sometimes it's just bad code.
Hmm. I wonder how someone punishes code for being bad?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply