January 15, 2004 at 1:03 pm
I am using SQL Server 2000 sp3a and have a linked server to a Oracle 8.1.6 dbase. I am trying to load data from the Oracle
table into a SQL table using transactions and receive the following error:
"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]."
Thanks in advance!!
January 15, 2004 at 1:18 pm
Is Oracle client network component installed in SQL Server?
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106&Product=sql2k
January 16, 2004 at 4:54 am
hi!
try turning on MS DTC (distributed transaction coordinator) on your SQL server machine.
best regards, chris.
January 16, 2004 at 7:15 am
MS DTC is started on the SQL Server. Also, below are the registry settings for [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
OracleXaLib=xa73.dll
OracleSqlLib = SQLLIB.dll
OracleOciLib=ociw32.dll
Changing registry settings always makes me a bit nervous.
I can query the tables on the linked server fine, I just get that error when I try to use an implicit transaction in my DTS Package.
Thanks!
January 16, 2004 at 7:17 am
Can you show your codes here?
January 16, 2004 at 8:03 am
Well, here is part of my code with the transaction (if I didn't use the DTS task)
begin tran
truncate table cs_ord
go
insert into cs_order_amts_all(Order_Num, Amount)
SELECT T1.SO as Order_Num,
CASE WHEN (SUM (T1.C_CHORHI_CHANGE_NUM = '0')
THEN (T1.AMT)
ELSE (T1.CHANGE_AMT)
END as Amount
FROM C_CHOR T1 LEFT OUTER JOIN CS_OLD_ORD T2
ON T1.SO = T2.OLD_ORDER_NUM
where ltrim(t1.so) not like 'B%'
GROUP BY T1.SO,OLD_ORD
ORDER BY SO
go
I get the error: 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].
If I don't use the transaction (begin tran) it works fine.
thanks.
January 16, 2004 at 8:16 am
See reply inline.
begin tran
truncate table cs_ord
go --- why do you need go here?
insert into cs_order_amts_all(Order_Num, Amount)
SELECT T1.SO as Order_Num,
CASE WHEN (SUM (T1.C_CHORHI_CHANGE_NUM = '0')
THEN (T1.AMT)
ELSE (T1.CHANGE_AMT)
END as Amount
FROM C_CHOR T1 LEFT OUTER JOIN CS_OLD_ORD T2
--- I assume C_CHOR or CS_OLD_ORD are the tables in ORACLE database. I don't see you use four-part name convention to access linked the table.
ON T1.SO = T2.OLD_ORDER_NUM
where ltrim(t1.so) not like 'B%'
GROUP BY T1.SO,OLD_ORD
ORDER BY SO
go
Here is my simple example.
begin tran T1
insert into actions select top 10 * from EA..EXAV.ACTIONS
commit tran T1
January 16, 2004 at 8:57 am
Actually, I am truncating cs_ord and then inserting into cs_ord (typo)
cs_old_ord is a table on SQL Server
C_CHOR is a view on SQL Server of an Oracle table (
CREATE VIEW dbo.C_CHOR
(SALES_CCN,SO,C_CHORHI_ORDER_TYPE,C_CHORHI_CHANGE_NUM,CHANGE_DATE,AMT,CHANGE_AMT,BOOK_DATE)
as
Select SALES_CCN,SO,C_CHORHI_ORDER_TYPE,C_CHORHI_CHANGE_NUM,CHANGE_DATE,AMT,CHANGE_AMT,BOOK_DATE
from GLOV_ORAC..GLOV_PRODQ.C_CHORH
begin tran
truncate table cs_ord
go
insert into cs_ord(Order_Num, Amount)
SELECT T1.SO as Order_Num,
CASE WHEN (SUM (T1.C_CHORHI_CHANGE_NUM = '0')
THEN (T1.AMT)
ELSE (T1.CHANGE_AMT)
END as Amount
FROM C_CHOR T1 LEFT OUTER JOIN CS_OLD_ORD T2
ON T1.SO = T2.OLD_ORDER_NUM
where ltrim(t1.so) not like 'B%'
GROUP BY T1.SO,OLD_ORD
ORDER BY SO
go
thanks.
January 16, 2004 at 9:04 am
begin tran
truncate table cs_ord
go --- You need remove 'go' here from your codes.
insert into cs_ord(Order_Num, Amount)
SELECT T1.SO as Order_Num,
CASE WHEN (SUM (T1.C_CHORHI_CHANGE_NUM = '0')
THEN (T1.AMT)
ELSE (T1.CHANGE_AMT)
END as Amount
FROM C_CHOR T1 LEFT OUTER JOIN CS_OLD_ORD T2
ON T1.SO = T2.OLD_ORDER_NUM
where ltrim(t1.so) not like 'B%'
GROUP BY T1.SO,OLD_ORD
ORDER BY SO
go
January 16, 2004 at 9:36 am
I removed 'go' from my code and am still receiving the error-- (by the way--thanks for your assistance on this!)
still stumped..
January 16, 2004 at 11:50 am
If you are running WIN2003 then try checking the following.
Open Component Services, Drill down to "My Computer" and right click it. Open the Properties box and click on the MSDTC tab. Then click on the Security Configuration button. Make sure that the check boxes are checked appropriately (mine are all checked right now) and that the DTC Login Account is set to NT AUTHORITY\NetworkService. Note: You may have to reboot the machine for these changes to take affect.
You might also want to add
SET XACT_ABORT ON
before beginning your transaction.
If all this doesn't work I would give PSS a call as you have a configuration problem on your machine that will not be trivial to figure out.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 16, 2004 at 2:13 pm
We are running WIN2K SP 3 and I also tried SET XACT_ABORT ON.
I would like to avoid calling PSS for support at this time. In an above posting, I listed my registry settings. Is that an issue? I can still query the Oracle tables, which is why I don't think that is a problem. I know they changed the start up accounts for sql server services- would that be an issue???
thanks.
January 16, 2004 at 3:53 pm
Did you read this KB article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply