Proc is locking up...

[flaniganmj]

what would cause the following to lock up:

set nocount on
set implicit_transactions on 
delete from blanca.obu.dbo.tglTrialBalWrk
commit transaction

and the below works fine:

set nocount on
delete from blanca.obu.dbo.tglTrialBalWrk

is there a server setting that i am missing?



Regards,

mjf

 

[NoCoolHandle]

When executing a transaction against a linked server you need to explicitly begin a distrubuted transaction.

I.E.

set nocount on
Begin Distributed Tran
delete from blanca.obu.dbo.tglTrialBalWrk
commit tran

Implicit transactions are for local databases where all logs are available. Distributed transactions allow you to span server boundarys.

Rob

 

[NoCoolHandle]

Please ignore my last post. It seems it wasn't correct.

I started out just checking my syntax. (never a bad thing)
and to my surprise when I tried to explicitly begin a distributed transaction it hung (well kind of... it ended up timing out after about 30 sec to a min with the following error...

'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a

.. Did some digging. A number of q articles and some more tests.

It seems that the issue is around the distributed transactions coordiantor's network permissions. I was doing my tests from my notebook (main dev system) which is not a member of the Domain of my linked server. (a stand alone server W2K advanced server system)

Well to cut a long story shortish.. I never did get things working from my laptop, however my desktop (which is a member of the domain) had absolutly NO PROBLMES with either the "Begin Distributed Transaction" or the "Set IMPLICIT_TRANSACTION ON" syntax. Infact they both worked in a fine transactional manor. (implicit trancactions on my notebook hung forever)

So.... the issue you are experiencing is NETWORK related and probably has something to do with the way your DTC connects (startup account)..

HTH


Rob