LE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Last Post 07 May 2009 09:51 AM by phsatech. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
phsatech
New Member
New Member

--
07 May 2009 09:51 AM
We have a linked Server setup and one of the developers was trying to run:

BEGIN TRANSACTION

DELETE FROM PS_PH_REQ_HR_COMBO

INSERT INTO PS_PH_REQ_HR_COMBO
( BUSINESS_UNIT
,DEPTID
,DEPT_DESCR
,ACCT_CD
,ACTION_TYPE
)
SELECT DISTINCT B.BUSINESS_UNIT
, C.DEPTID
, RTRIM(C.DEPTID) + '-' + RTRIM(E.SETID) DEPT_DESCR
, D.ACCT_CD
, 'A' ACTION_TYPE

--FROM [VSQLFSM\INSTFSM].[FS84PRD].dbo.PS_COMBO_DATA_TBL A
FROM [ZRVPSS03].[FS84PTST].dbo.PS_COMBO_DATA_TBL A
, PS_BUS_UNIT_TBL_HR B
, PS_DEPT_TBL C
, PS_ACCT_CD_TBL D

, PS_SET_CNTRL_REC E

WHERE A.EFFDT_TO > GETDATE()
AND A.VALID_CODE='V'
AND A.EFFDT_OPEN = 'Y'

AND B.BUSINESS_UNIT_GL = A.COMBINATION
AND B.ACTIVE_INACTIVE = 'A'

AND E.SETCNTRLVALUE = B.BUSINESS_UNIT
AND E.REC_GROUP_ID = 'HR_01'
AND E.RECNAME = 'DEPT_TBL'

AND C.SETID = E.SETID
AND C.DEPTID = A.DEPTID
AND C.EFF_STATUS = 'A'
AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED
WHERE C_ED.SETID = C.SETID
AND C_ED.DEPTID = C.DEPTID)

AND D.ACCT_CD = RTRIM(A.FUND_CODE) + RTRIM(A.OPERATING_UNIT) + CASE WHEN A.PROJECT_ID = ' ' THEN '' ELSE '/' + RTRIM(A.PROJECT_ID) END
AND D.EFF_STATUS = 'A'
AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_ACCT_CD_TBL D_ED
WHERE D_ED.ACCT_CD = D.ACCT_CD)

after it executes we get:

(3535 row(s) affected)

Server: Msg 7391, Level 16, State 1, Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[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].


Any ideas? We thought we ran this previously without any errors. Is there anything that can be added to the statement so its not dependent on configuring DTC?


Acceptable Use Policy
---