Service Broker Transactions

Question

Service Broker Transactions

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

So much reading 🙂

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

zymos SSCommitted Points: 1960 More actions · Answer 2

Great question. I have sure learnt more on the 'Uncommitable transaction' topic.

URL: http://technet.microsoft.com/en-us/library/ms179296.aspx

Thank you.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

As usual when service broker is involved the code is bit lengthy, but it is a good question.

In my experience errors like these are less likely if you get used to writing your try-catch blocks like this:

BEGIN TRANSACTION trn_MYTRAN;

BEGIN TRY

-- Anything that can fail goes here

COMMIT TRANSACTION trn_MYTRAN;

END TRY

BEGIN CATCH

IF XACT_STATE() > 0

ROLLBACK TRANSACTION trn_MYTRAN;

ELSE IF XACT_STATE() < 0

ROLLBACK TRANSACTION;

-- Do your further error handling/reporting here.

END CATCH

The commit is executed only if everything went fine, the rollback is only executed whenever something fails. When something failed, you will have to assess how badly things went wrong; i.e. you need to check what the status is of your transaction. The connection is still there because otherwise we would not get into the catch block, so we don't need to check for that, but the transaction may be rolled back, invalid or valid upon entry into the catch block. So this is why I check XACT_STATE(). 1 says: transaction is valid. 0 = not in a transaction and -1 = in a doomed transaction. Take the appropriate action for each situation and you'll be able to recover from almost all situations.

When a doomed transaction exists you can no longer call "rollback transaction <name>". A doomed transaction no longer is named, i.e. if xact_state() returns -1, do a rollback tran, without the name, or you'll raise yet another error...

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 4

I had no idea so I just picked the longest one 🙂

Thanks for the question. Back to the books!!

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 5

Took a guess and got it right. Thanks for submitting.

http://brittcluff.blogspot.com/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Really?

"Here's 120+ lines of SQL. What happens if there's an error on Line #45?"

Brutal. Seriously.

[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]

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

Never used "Service Broker Transactions" b4, so got it wrong 😎

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant