Dynamic SQL AND Distributed Transactiongreenspun.com : LUSENET : SQL Server Database Administration : One Thread |
Hi Eric,I am using SQL 2000 server to store the job status from all types of servers like SQL, Sybase, Oracle. During this process, I have found a strange thing ( for me ). When I include "INSERT INTO" into the dynamic SQL statement , SQL Server is able to extract the data from Oracle server and into into SQL hash table. But, if I specify only the "SELECT " statement in dynamic SQL and use "insert into #test exec (@cmd)". It is generating the following msg.
------------------------------------ Server: Msg 7391, Level 16, State 1, Line 1 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]. -------------------------------------
Sample stored procedures
Proc 1 - This one fails
create proc test_procA as begin create table #test (job_name varchar(20),status varchar(20),event_date datetime) declare @link_server varchar(20) select @link_server = 'USLONCO10'
declare @sql varchar(1000) Set @sql = N'Select * from OPENQUERY(ORA_SEVER,''select * from MONITOR.JOB_MONITOR'')'
insert into #test exec (@sql) select * from #test end
End of Proc 1
Proc 2 - This one works fine.
create proc test_procB as begin create table #test (job_name varchar(20),status varchar(20),event_date datetime) declare @link_server varchar(20) select @link_server = 'USLONCO10'
declare @sql varchar(1000) Set @sql = N'insert into #test Select * from OPENQUERY(ORA_SERVER,''select * from MONITOR.JOB_MONITOR'')'
exec (@sql) select * from #test end
End of Proc 2
My question is, is this the expected behaviour, if so, why, or is it a bug ?
Thanks a ton.
Regards
Sudhakar
-- Anonymous, February 04, 2004
Sudhakar,In the case of Proc 1, insert into #test exec (@sql), there is a modification of a table (being inserted) in a transaction involving the distributed query. Hence, unlike Proc 2, this needs a Distributed Transaction in order to run this query. For some, reason, in your case the MSDTC is not able to process this distributed transaction across the the server.
- Make sure that MSDTC is running on both the servers.
- Is there any firewall between the two servers?
- If you are using a firewall, make sure that the port#135 (used by RPC) is open for outgoing DTS packets.
Hope this helps,
Eric
-- Anonymous, February 10, 2004