December 18, 2006 at 7:49 am
I have my SQL Server linked to Oracle database and have been using the openquery method successfully. I am having a problem when I try to execute a sproc that uses openquery to get to the linked server data AND contains a transaction, even if the openquery is NOT within the transaction. Here is the sql:
--connect direclty to Oracle database and put data in temp table
select *
into #t_ps_dwst_load_stat
from
openquery(DWConnect,
'select table_name, tablespace_name, last_loaded, num_rows
FROM PS_DWST_LOAD_STAT')
--transaction to refresh data
begin tran load_data
--check to make sure there is data in temp table. if so, delete data in sql server regular table
if (select count(*) from #t_ps_dwst_load_stat) > 0
delete from t_ps_dwst_load_stat
--insert the data from our temp table into the regular table.
insert into t_ps_dwst_load_stat
(table_name, tablespace_name, last_loaded, num_rows)
select table_name, tablespace_name, last_loaded,num_rows
from #t_ps_dwst_load_stat
--commit or rollback the transaction
if (@@error=0)
begin
Commit tran load_data
end
else
begin
rollback tran load_data
end
--clean up
drop table #t_ps_dwst_load_stat
The message I get is:
(703 row(s) affected)
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
Msg 7391, Level 16, State 1, Procedure p_load_t_ps_dwst_load_stat, Line 23
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
I get the 703 rows affected because it does actually populate my temp table #t_ps_dwst_load_stat. Then I get the error about MSDAORA and distributed transactions when it tries to do the transaction.
If I take the transaction wrapper out, it works fine.
Thoughts?
TIA.
--Susan
December 18, 2006 at 5:36 pm
Use
BEGIN DISTRIBUTED TRAN
COMMIT DISTRIBUTED TRAN
December 19, 2006 at 1:09 pm
Using Begin Distributed Tran did not solve the problem. However, I did solve the problem by using a table variable instead of a temp table. I don't know why, but it works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply