November 10, 2008 at 4:00 pm
I am writing a client-called stored procedure that is (intentionally) leaving a Transaction open in xome caese, when it exits to the Client. In other cases it will be called by the client with one of these still open transactions and will close it as needed by the application logic.
However, any time that the beginning and ending @@TRANCOUNT's are not the same (whether greater than or less than) I get the Error 266: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing".
Is there anyway that I can suppress this error?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 5:24 pm
I don't have an answer for you because I've never had that as a requirement, but I do have a question:
What is supposed to happen if the second application (that is supposed to finish up) fails? Is SQL just supposed to wait forever?
Good luck.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 10, 2008 at 6:59 pm
Not sure how you would do this, but it sounds like the application should control the application, starting it and ending it (commit or rollback) and that the stored procedure should operate inside of that transaction.
November 10, 2008 at 7:07 pm
A TRY/CATCH in the calling context will deal with it. As long as you deal with it before the end of the batch you should be okay.....
begin try
exec barry
END TRY
begin catch
print 'fun huh'
end catch
select @@trancount
select * from kk1
while @@trancount>0
begin
print 'rolled back'
rollback
end
where stored proc Barry is:
create proc barry
as
begin
begin tran
create table kk1(id int primary key)
insert kk1
select top 50000 row_number() over (order by nc1.object_id)
from sys.all_columns nc1, sys.all_columns nc2
END
----------------------------------------------------------------------------------
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?
November 10, 2008 at 8:45 pm
bhovious (11/10/2008)
What is supposed to happen if the second application (that is supposed to finish up) fails? Is SQL just supposed to wait forever?
There is only one application, making multiple calls. The client application must make the next call within 30 seconds. If it does not or cannot, it will eventually be killed and restarted.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 8:55 pm
Lynn Pettis (11/10/2008)
Not sure how you would do this, but it sounds like the application should control the application, starting it and ending it (commit or rollback) and that the stored procedure should operate inside of that transaction.
Yes, normally we would have the Client be smart and control the Server which would be dumb. However in the case the control logic must be inverted: the Server is Smart and the Client is dumb. This is a long-standing development technique involving stamp coupling, though it is rarely seen in database servers: both CallBacks and parametized Event signaling are examples from GP programming.
In this cases the Stored Procedure(on the server) is smart and knows when transaction must be started, committed and rolled-back. I just figured because this message was only a warning and not an actual error, that there might be a way to suppress it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 9:09 pm
Matt Miller (11/10/2008)
A TRY/CATCH in the calling context will deal with it. As long as you deal with it before the end of the batch you should be okay.....
Yes, but that is exactly what I am trying to avoid, Matt. Of course I could have the caller catch the error, but I want the proc to suppress it in the first place: I do not want it to return an error in this case.
For every other non-fatal run-time error, I can CATCH it in the sproc itself and deal with it there. The possible (but rare) fatal errors are easy, I just have my Client service log it to the server Event Log, and then restart the service. This is the only odd message that I have hanging out there, and it is a normal operating condition, not an exception at all as far as the application logic is concerned.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 9:11 pm
This may not be ideal - but you could set up a DB version of a dead man's switch. That is - set up a WHILE loop with a WAITFOR, waiting until an action occurs (like a row being inserted into a DeadManSwitch table) then resume the commit.
If the batch finishes before you commit- you get the old
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
----------------------------------------------------------------------------------
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?
November 10, 2008 at 9:16 pm
Or - use a WAITFOR with a Service Broker Group conversation handle....No loop.
The initial catch just suppresses the error (it didn't abort the trx at all). you just have to handle the transaction before the batch ends.....
----------------------------------------------------------------------------------
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?
November 10, 2008 at 9:23 pm
rbarryyoung (11/10/2008)
Matt Miller (11/10/2008)
A TRY/CATCH in the calling context will deal with it. As long as you deal with it before the end of the batch you should be okay.....Yes, but that is exactly what I am trying to avoid, Matt. Of course I could have the caller catch the error, but I want the proc to suppress it in the first place: I do not want it to return an error in this case.
For every other non-fatal run-time error, I can CATCH it in the sproc itself and deal with it there. The possible (but rare) fatal errors are easy, I just have my Client service log it to the server Event Log, and then restart the service. This is the only odd message that I have hanging out there, and it is a normal operating condition, not an exception at all as far as the application logic is concerned.
The only way I know how to do that is to call the proc from within ANOTHER proc. Which sucks, I know.
I know you deal with SB a lot - maybe that's the way to attack this (just start something running with Service broker with a started transaction, then simply put it on ice with a WAITFOR "conversation", then resume and commit). That would allow you to keep working around it.
----------------------------------------------------------------------------------
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?
November 10, 2008 at 9:25 pm
rbarryyoung (11/10/2008)
I am writing a client-called stored procedure that is (intentionally) leaving a Transaction open in xome caese, when it exits to the Client. In other cases it will be called by the client with one of these still open transactions and will close it as needed by the application logic.However, any time that the beginning and ending @@TRANCOUNT's are not the same (whether greater than or less than) I get the Error 266: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing".
Is there anyway that I can suppress this error?
as per my understanding to the problem, i did the small experiment
i executed these commands
begin tran
select * from bank
begin tran
select * from customer
and when i supply this command
Select @@trancount
it shows me 2 transactions to be commit or rollback
on the basis of this count i execute a while loop inside which i called the Commit tran or rollback tran as per the requirement.
may be im wrong . but may be this can help you
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 10, 2008 at 9:31 pm
use WAITFOR is one of the good option.
November 10, 2008 at 9:56 pm
Matt Miller (11/10/2008)
Or - use a WAITFOR with a Service Broker Group conversation handle....No loop.
OMG, you are killing me here Matt! I am using Service Broker with WAITFOR, that's how I got into this spot... :w00t:
Serioulsy though, if there is no easy way to do this, I can have the client handle it, I was just trying to avoid that...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 9:59 pm
Matt Miller (11/10/2008)
The only way I know how to do that is to call the proc from within ANOTHER proc. Which sucks, I know.
Oh I would be OK with that (I do it all the time with Dynamic SQL), but this is the one case where that will not work, because now the calling proc itself gets the error too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 10:06 pm
krayknot (11/10/2008)and when i supply this command
Select @@trancount
it shows me 2 transactions to be commit or rollback
on the basis of this count i execute a while loop inside which i called the Commit tran or rollback tran as per the requirement.
may be im wrong . but may be this can help you
Nope. The problem is not that I have unclosed transaction that I need to close. The problem is that I NEED to keep a transaction unclosed because I will not know how it should be disposed until the next time that the client calls my server proc.
I.E., I cannot just close these transactions because I need them; but I do not want to throw an error when I am doing this: I just need to suppress the error, not change the transaction state.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply