Error using Begin Distributed Transaction.....

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  August 17, 2009 at 5:27 pm

  #208819

  Hello,

  I'm getting the following error when using the begin distributed transaction in the following statement:

  But when i do not use the begin distributed transaction the error didn't appears

  CREATE TABLE [dbo].[EXAMPLE_TBL] (

  [EX_COD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [EX_DESC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [EX_NUM] [bigint]

  ) ON [PRIMARY]

  GO

  Error with distributed tran:

  BEGIN DISTRIBUTED TRAN

  DELETE FROM EXAMPLE_TBL

  INSERT INTO EXAMPLE_TBL Select * from openquery(MY_LNKSRV,'SELECT * FROM OUT_TBL')

  COMMIT TRAN

  Server: Msg 7391, Level 16, State 1, Line 4

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

  OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

  output without distributed tran

  --BEGIN DISTRIBUTED TRAN

  DELETE FROM EXAMPLE_TBL

  INSERT INTO EXAMPLE_TBL Select * from openquery(MYPT_TCS,'SELECT * FROM PTC_CANAL')

  --COMMIT TRAN

  SELECT * FROM EXAMPLE_TBL

  EX_COD EX_DESC EX_NUM

  --------- -------- ------------

  10000 DESC_EX 1

  Can you help me because i'm lost with this error.

  Thanks and regards,

  JMSM 😉

• Danie Loots

  Right there with Babe

  Points: 740

  More actions

  August 19, 2009 at 6:41 am

  #1041459

  Hi,

  is the Oracle instance you are querying running on Windows? If so, the default in Windows Server 2003 R2 + is to disable support for remote transactions. See http://support.microsoft.com/kb/817064 for Windows 2003 and http://technet.microsoft.com/en-us/library/cc753620(WS.10).aspx for Windows 2008.

  The specific error you are getting is due to MSDTC not being able to contact the transaction coordinator on the remote instance. Has this worked before?