May 28, 2004 at 2:28 am
Dear SQL Masters,
When I debug a stored procedure I see that before the following code (see below) @@TRANCOUNT has a value of 0 and after stepping through @@TRANCOUNT has a value of 1. Which, unfortunately, messes up my transaction management.
Is this normal behaviour or did I make as mistake?
SELECT @intRowcount = COUNT(status) FROM invoice WHERE invoice_id IN
(SELECT ID FROM OPENXML (@Doc, 'ROOT/INVOICE',1) WITH (ID INT '@ID') ) AND status = 'I '
Thanks for your attention,
Gerry S.
May 31, 2004 at 8:00 am
This was removed by the editor as SPAM
May 31, 2004 at 9:24 am
Are implicit transactions turned on? Haven't noticed this, but I don't do much XML work. I'll have to take a look.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 1, 2004 at 1:41 pm
I've never noticed this but then I always have a distributed transaction running when I'm reading XML. I have never had a problem with a transaction count when doing this. Try adding "SET XACT_ABORT ON" before starting your transaction and see if that helps.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
June 4, 2004 at 4:50 am
Gary and Steve,
I should have mentioned that I ran the script in debug mode with AUTOROLLBACK ON. Which, I suppose, is also the answer why @@TRANCOUNT yielded an unlike number.
Apparently SQL Server 2000 encapsulates the autorollback debug session with a transaction of its own.
Thank you very much for your reply.
Gerry.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply