February 22, 2016 at 10:15 am
First I want to say that I inherited this. 🙂
Sample script is in the attachment.
Some of our sp's call an sp in the CATCH block to do some work before rolling back any transaction(The rollback is in this called sp).
In the TRY block it either starts a transaction or it does a save transaction based on if there is an outer transaction.
When the catch block is executed and it's using the sp to do a rollback inside the sp I get the mismatch error.
If I take the same script inside the sp and put it directly in the catch block without calling the sp then it works fine.
If the Transaction is persistent throughout the session why is it giving me the error in the SP call?
The script below creates all the sample sp's and runs the 2 scenarios at the end.
Can someone explain the reason why it's giving me the mismatch error in my usp_TestSPROC1 but not in usp_TestSPROC2?
EDIT NOTE:
I can't post the code here, our web filter is blocking things that look like code. I'm trying to attach my sample as a text file but not sure if that works. If not I'll edit this post later from home and add my script.
Basic setup is an sp that starts the transaction in a try/catch block, It has a begin tran, raise error and commit in the try block. Catch block as a call to an sp that has rollback.
This is what gives me the mismatch.
The same setup with the rollback in the catch block without the call to the sp works fine.
Why it's calling the rollback from the SP:
The main sp processes a bunch of accounts. If there's an error it logs that into a table. What the rollback sp is doing is saving that error into a table variable, doing a rollback and then writing the data back out to the process table so we can have that log without it getting rolled back. I assume it was done this way so they can reuse it without having to write the same script over and over again.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
February 22, 2016 at 11:36 am
Ok nevermind. I found the cause... didn't know that the 266 get raised every time the tran count is different before and after executing an sp. That makes sense I guess. I'm just going to wrap it in a try/catch and "ignore" if the error is 266. Let me know if anyone has a cleaner solution, that doesn't require not using the sp.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply