0

Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a year.

ALTER PROCEDURE [dbo].[bsi_transfer_reallocate] @transfer_no int
AS  
BEGIN
BEGIN TRY  


[a few lines of code]

  BEGIN TRANSACTION REALLOCATE;


(over 700 lines of code)

  COMMIT TRANSACTION REALLOCATE;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION REALLOCATE;
    SET @dbg_msg = 'Transfer ' + CAST(@transfer_no AS varchar(8)) + ' aborted due to error condition with error number ' +
       CAST(ERROR_NUMBER() AS varchar(200)) + ' and message ' + ERROR_MESSAGE()
    INSERT INTO BATTERY_debug VALUES ('REALLOCATION Failure', GETDATE(), @dbg_msg)
    select  @dbg_msg
    RETURN (1)
END CATCH
END
return(0)
Improve this question
2
  • 4
    So if there is no BEGIN TRANSACTION being run, the culprit lies in "a few liones of code" most likely. How should we start debugging these lions? – LowlyDBA - John McCall Nov 6 '19 at 18:35
  • I hope you're using version-control because if you are you could compare this version of the stored procedure to find the tiny change that surely must have been made ... – Mike Robinson Nov 6 '19 at 19:11
4

Some errors automatically rollback the transaction. See generally Error and Transaction Handling in SQL Server

And if your error already rolled back the transaction, the first line of your catch block will throw this error.

So replace

  ROLLBACK TRANSACTION REALLOCATE;

with

 if @@trancount > 0 ROLLBACK TRANSACTION;

(Remove the 'REALLOCATE' label because this will NOT roll back only the nested transaction. A Nested Transaction can have a name, but cannot be individually rolled back. For that you would need a savepoint, but that's probably not the issue here).

Improve this answer
-1

I'd hazard a guess that an exception is being thrown by one of those "liones" ... causing the CATCH block to be executed before the BEGIN TRANSACTION statement occurred.

Improve this answer
4
  • [typo fixed] - that seems like a good chance. Maybe I can move the begin transaction up. I guess the lesson is that the transaction needs to begin right after the begin try. – Dean Myerson Nov 6 '19 at 19:38
  • "Down-votes notwithstanding," I really do think that you should (if possible) begin by looking for any source-code changes that might have occurred with regards to this procedure, before moving on to a careful perusal of database-server logs to discern if the original apparently-well-tested logic is now suddenly encountering "a brand-new scenario." (Always a possibility, unfortunately.) You really do need to know – first – if the source-code that you are now looking at has recently changed, however slightly. – Mike Robinson Nov 7 '19 at 1:44
  • ... my point being: "maybe the proc as-written actually always had this deficiency." You really need to know right-away whether your last years' faith in it was justified. – Mike Robinson Nov 7 '19 at 1:45
  • There have been no changes since we last had a successful rollback due to an error (a different error). This is why the problem being caused by the code between the begin try and the begin tran makes sense and is the very likely cause of the problem. PAst errors did not occur in that code and so everything worked, I consider my question answered and really appreciate all the responses. – Dean Myerson Nov 7 '19 at 18:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.