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

Answers

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