Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sondc
Starting Member
7 Posts |
 Posted - 2009-08-19 : 07:49:55
|
| Dear expertI have a case like this:Oracle 10g run on server A with OS Win 2K3 SP2SQL 2000 SP4 run on server B with OS Win 2k3 SP1I can link to server A from SQL 2000 and can execute statement like this from QA successful:Select * from openquery(A,'Select * from wo_mstr where rownum <10')But I receive error Msg 7391"The Operation couldnot be performed because the OLD DB Provider 'MSDAORA' was unable to begin a distributed transaction.OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]"when i execute this statement with "begin tran" and "commit tran":Begin Tran vbTestSelect * from openquery(A,'Select * from wo_mstr where rownum <10')Commit Tran vbTestRPC and MSDTC are OK. (Tested by DTCPing)Please help meC.Son |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-19 : 10:19:58
|
| either you need to configure Oracle to participate in distributed transactions, or you need to remove the transaction from your SQL statement. |
 |
|
|
sondc
Starting Member
7 Posts |
Posted - 2009-08-19 : 21:53:51
|
| Thank russel for your recommendI will explain more for you to understand my statusWe can do that (with transaction) in server that run SQL2005. So, do we need to change in oracle ? please advise me.It's ok to remove "transaction" for "select" statement only.But we use data in oracle for updating or inserting to SQL2000 by using store procedure.So we need commit tranEx.BEGIN TRAN vbTest insert into tblWeb_wo select CatCode, WONo,'08-21-2009' as DataDate from openquery(fovsevr4,'select wo_lot as WONo, pt_prod_line as CatCode from wo_mstr inner join pt_mstr on upper(pt_domain) =upper(wo_domain) and upper(pt_part)=upper(wo_part) where upper(wo_status) = ''F'' and upper(pt_domain)=''FOV'' and pt_prod_line >=1100') Where WoNo Not in (select WONo from tblweb_wo)COMMIT TRAN vbTestNow, We can not do direct like this, we have to select data in oracle first, then insert to SQLThis is a simple statement, in our application, there are many complex statement.This trouble just happen when we upgrade OS of SQL server from Win2K to win 2K3. Before this, it works.Please give us your idea |
|
|
|
gvphubli
Yak Posting Veteran
54 Posts |
|
|
|
|
|
|
|
Error when get data from link server