SQL Server Performance Forum – Threads Archive
Problem with distributed transaction SP.<br />Hi,<br /><br />I am working on have 02 machine WinXP Professional with SP2 and<br />SQL2k developer with SP4 install. They are machine A and B.<br /><br />I have enable DTC and disable WinXP firewall plus enable the port 135 for MS DTC.<br />I have enable setup linked server,MB from machine A<br /><br />I can execuate local SP and remote server SP without any problem.<br /><br />However, when I try to execuate local SP like the pseud code<br />below that call local SP and remote SP, it will give me the error.<br />I ha<br /><br /><br />Server: Msg 7391, Level 16, State 1, Procedure a_test_Update, Line 19<br />The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.<br />[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]<br />OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ITransactionJoin::JoinTransaction returned 0x8004d00a].<br /><br /><br />>><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />SEUD CODE<<<br /><br />SET XACT_ABORT ON<br />SET REMOTE_PROC_TRANSACTIONS ON<br />BEGIN DISTRIBUTED TRANSACTION<br /><br /> <br /> exec local SP<br /> <br /> exec remote SP<br /> <br />COMMIT TRANSACTION<br /><br /><br />Please advise me how to fix this error. I have try out numerous<br />method from MS Knowledge based but all in vain.<br /><br />Thank you<br /><br /><br /><br />
Make sure MSDTC is switched on and KBAshttp://support.microsoft.com/kb/306212 &http://support.microsoft.com/kb/306212/en-us for refresh of steps to resolve it. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
Port 135 is not enough for MSDTC, it also uses random higher ports. These can be locked down to a specific range: INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (Q250367)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q250367 Also see: