Distributed Transaction Issue

• DBABC

  SSCarpal Tunnel

  Points: 4813

  More actions

  April 5, 2011 at 11:42 am

  #235079

  I have a triger (T1) on Server A (SS 2000), that calls a local procedure (P1) which then calls a remote procedure (P2) on Server B (SS 2008 R2). P2 inserts data from Server A to Server B.

  I am getting following error while the triger is executed.

  [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

  OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

  Msg 7391, Level 16, State 1, Procedure usp_UpdatePriceTable, Line 4

  The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

  When i run P1 manually from Server A, it works fine without any issues.

  I have confirmed and MS DTC is enabled and configured properly on both Server A & B as follows.

  From "administrative tools" -> "component services" -> "computers" ->

  "my computer" , "local dtc" right click "properties"

  goto MSDTC tab and select "security configuration", enable checkbox

  for "network dtc access" and "allow outbound".

  Check No Authentication Required.

  Can someone please help?

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 5, 2011 at 1:14 pm

  #1307664

  Try this post.

  Then in the spirit of your name you can try this one :-D:-D:-D

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/