February 11, 2014 at 1:01 pm
Hello! I am a newbie to using transactions and I'm having a problem... I have a stored procedure that I'm trying to implement error handling on. It works fine when there are no errors... but when it encounters an error and it should be rolling back, I get the following:
Cannot roll back UPDT_DATA. No transaction or savepoint of that name was found.
I do not have any nested transactions...but I'm assuming because of the dynamic IF structure in this, that it is hiccuping because of that? Do I need save points and how exactly would I add them?
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION UPDT_DATA
IF @CHANGE_TYPE IN ('BEGINNING & TERMINATING FY','RIC & BACK5')
BEGIN --#2
IF @CHANGE_TYPE = 'RIC & BACK5'
BEGIN --#3
SET @SQL = 'UPDATE DEF_TABLE_NAME
SET ' + @SET_FIELD_NAME + ' = ' + @NEW_FIELD_VALUE + ',' + @SET_FIELD_NAME_2 + ' = ' + @NEW_FIELD_VALUE_2 + '
FROM DEF_TABLE_NAME A, ANOTHER_TABLE B, ANOTHER_TABLE_2 C
WHERE C.ISSUE_NUM = B.ISSUE_NUM
AND A.SSN6 = C.SSN
AND FIELD_NAME IN ' + @WHERE_FIELD_VALUE + '
AND SEND_MAIL = 1
AND B.ISSUE_NUM = ' + CONVERT(VARCHAR(9),@ISSUE_NUM) + ''
--SELECT @SQL --FOR TESTING.
EXEC (@SQL)
END --#3
ELSE
BEGIN--#4
SET @SQL = 'UPDATE DEF_TABLE_NAME
SET ' + @SET_FIELD_NAME + ' = CONVERT(CHAR(4),' + @NEW_FIELD_VALUE + '),' + @SET_FIELD_NAME_2 + ' = CONVERT(CHAR(4),' + @NEW_FIELD_VALUE_2 + ')
FROM DEF_TABLE_NAME A, ANOTHER_TABLE B, ANOTHER_TABLE_2 C
WHERE C.ISSUE_NUM = B.ISSUE_NUM
AND A.SSN6 = C.SSN
AND FIELD_NAME IN ' + @WHERE_FIELD_VALUE + '
AND SEND_MAIL = 1
AND B.ISSUE_NUM = ' + CONVERT(VARCHAR(9),@ISSUE_NUM) + ''
--SELECT @SQL --FOR TESTING.
EXEC (@SQL)
SET @SQL = 'UPDATE DEF_TABLE_NAME_2
SET ' + @SET_FIELD_NAME + ' = CONVERT(CHAR(4),CASE WHEN ' + @NEW_FIELD_VALUE + ' = ''OPEN'' THEN 0 ELSE ' + @NEW_FIELD_VALUE + ' END),' + @SET_FIELD_NAME_2 + ' = CONVERT(CHAR(4),' + @NEW_FIELD_VALUE_2 + ')
FROM DEF_TABLE_NAME_2 A, ANOTHER_TABLE B, ANOTHER_TABLE_2 C
WHERE C.ISSUE_NUM = B.ISSUE_NUM
AND A.SSN = C.SSN
AND FIELD_NAME IN ' + @WHERE_FIELD_VALUE + '
AND SEND_MAIL = 1
AND B.ISSUE_NUM = ' + CONVERT(VARCHAR(9),@ISSUE_NUM) + ''
--SELECT @SQL --FOR TESTING.
EXEC (@SQL)
END --#4
END --#2
COMMIT TRANSACTION UPDT_DATA
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION UPDT_DATA
EXECUTE DB_AUDIT.AUDIT.SP_LOGERROR NULL,@ERRORLOGID = @ERRORLOGID OUTPUT
END CATCH
SELECT @ISSUE_NUM, @REQUEST_TYPE, @CHANGE_TYPE
SET XACT_ABORT OFF
This is just a snippit of the procedure and it does have a ton more IFs and nested IFs... please let me know if you need more info to answer my question.
Thanks in advance!
February 11, 2014 at 2:49 pm
You most likely do have nested transactions, either implicitly or explicitly.
Just remove the transaction name from the ROLLBACK, which is not meaningful for SQL Server anyway:
ROLLBACK TRANSACTION /*UPDT_DATA*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 11, 2014 at 3:13 pm
Removing the transaction name does not rectify the situation.
The only thing that I could think of was we do have an if statement that calls another stored procedure with a transaction in it. Could that be the issue?
February 11, 2014 at 3:28 pm
amy26 (2/11/2014)
Removing the transaction name does not rectify the situation.The only thing that I could think of was we do have an if statement that calls another stored procedure with a transaction in it. Could that be the issue?
Huh?
Removing the name from all ROLLBACKs does not get rid of error:
"Cannot roll back UPDT_DATA. No transaction or savepoint of that name was found."?
?? How is that possible ??
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 11, 2014 at 3:52 pm
Sorry let me clarify, removing the name changes the error to something else. I can do it again and provide the exact one when I'm back at my computer.
Also are you saying to not name the transaction at all or only remove the name from the rollback line?
February 11, 2014 at 6:12 pm
Ok, so I did some more digging cause I kept thinking about what you said that removing the name of the transaction should have made the error go away.... however it didn't. So, I kept poking around and I guess the error message that the front end application was giving was not actually the error that was happening in the database.
So, I ran the procedure from the backend and I got a weird error that said:
An Error Has Occurred In Stored Procedure sp_ErrorLog
And I was like hmmmm that isn't even erroring in my main procedure and realized it was erroring in my procedure that writes to my error log table. When I debugged that procedure, I found that the problem was actually then in a trigger that I have on the error log table and it was giving me a conversion failed on string value 'NONE' to int.
So, I fixed the problem in the trigger and poof the stored procedure now executes.
The thing I find odd is that in the error log table, it is capturing a problem with my main stored procedure (the one I posted here) with a syntax error near "FROM". However, it doesn't capture the stored procedure name in the error log. It just puts NONE because the ERROR_PROCEDURE() is NULL. Which, I would think it should be reporting the procedure name cause that's where the syntax error is....
so, anyhooo.... I appreciate your assistance, it made me think about what my real problem was. 🙂 And also, you were right I still had to remove the name of the transaction to make it work properly.
February 12, 2014 at 1:35 am
amy26 (2/11/2014)
The thing I find odd is that in the error log table, it is capturing a problem with my main stored procedure (the one I posted here) with a syntax error near "FROM". However, it doesn't capture the stored procedure name in the error log. It just puts NONE because the ERROR_PROCEDURE() is NULL. Which, I would think it should be reporting the procedure name cause that's where the syntax error is....
Try running error_procedure() in the catch block and passing the result as a parameter to the logging procedure.
And to explain the transaction naming error, for the most part, transaction names in SQL are pretty much comments, they have little use beyond that (unless you name and mark a transaction and intend to restore backups back to the marked transaction, or a few other lesser used scenarios)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 6:31 am
Thanks! I will try that.
And yea I know the naming thing isn't anything special. I just did it mostly cause I liked having it named... 😛 But just find it odd that I can name my other transactions in my other procedures without all the nested IF statements but simply not naming the transaction in my nested IF procedure fixed the issue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply