Problem with distributed transaction SP. | SQL Server Performance Forums

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 />&gt;&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />SEUD CODE&lt;&lt;<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
http://www.SQL-Server-Performance.Com
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:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13571
]]>