"Machine angered as developer changes @@trancount"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

"Machine angered as developer changes @@trancount"

Author

Topic

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-13 : 11:53:52

I'm trying to tidy up a connection by rolling back any open transactions it may have, here's the code for you to try

create procedure dbo.cu_ConnectionTidyUp
as
while @@trancount > 0
begin
rollback transaction
end
go
begin transaction
begin transaction
begin transaction
begin transaction
begin transaction
begin transaction
begin transaction
begin transaction
begin transaction
begin transaction

select @@trancount

go

exec dbo.cu_ConnectionTidyUp
--while @@trancount > 0 begin rollback transaction end

go

select @@trancount

go

as you can see all i want to do is kill the fake transactions i just opened. If you change the commenting so that it runs the plain SQL there is no problem, but in a proc i get an error.

I want to yell "Of course the transaction count has changed you dumb machine, thats what its supposed to do!", and then tear my hair out, but my colleagues might get upset and I think I'll go bald soon enough without helping mother nature.

Is there any way of stopping this behaviour, of should I just ignore the error, assume its done its job, which it will have, and carry on.

Thanks in advance

col

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-13 : 14:44:56

A couple things.

First, one rollback (with out a savepoint or transaction name) will clear all the transactions. I don't thinknyou need a procedure for that.

Second, if you look up the Error in BOL, they offer some work arounds. I would simply just type out IF @@TRANCOUNT > 0 ROLLBACK

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-14 : 05:42:04

Thanks, but i dont think I can just run the commands without a proc, security issues get me. Darned environment!

Looked at all the BOL stuff, just seems to say not to do it in the first place, not what I'm after.

I really do want to change the tran count. I think I'm just going to have to trap the error, check what it is, and carry on if its a 266.

I blame this all on darned connection pooling on SQL Server 7, but I dont want to turn it off because of performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources