February 21, 2007 at 9:48 am
Have a stored procedure with a single input parameter that returns a data set from an linked Oracle server; I can execute the SPROC any number of times, changing the parameter each time using sp_ExecuteSQL. I get the expected, combined result set. However, if I try to capture the resultset to a table (with INSERT) I get the following error:
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
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.
Does anyone have a suggestion on how to work around this problem?
February 21, 2007 at 10:46 am
I found these two dealing with the issue in both SQL Server 2000 and 2005, check to see what you need to change. Hope this helps.
http://support.microsoft.com/kb/306212
http://www.dotnetjunkies.com/WebLog/daveadamson/archive/2005/11/22/133928.aspx
Kind regards,
Gift Peddie
February 21, 2007 at 1:21 pm
Thanks for the reply. The second article sounded particularly useful so I set all the parameters as specified. However, it didn't help. Neither article specifically mentions INSERTing from a stored procedure (that executes a distributed query) although the error message is same.
February 21, 2007 at 2:05 pm
(Verify that the OCI versions are registered correctly as described earlier in this article.
Note If the registry entries are all correct, the MtxOCI.dll file is loaded. If the MtxOCI.dll file is not loaded, you cannot perform distributed transactions against Oracle by using Microsoft OLE DB Provider for Oracle or by using Microsoft ODBC Driver for Oracle. If you are using a third-party provider and you receive Error 7391, verify that the OLE DB provider that you are using supports distributed transactions. If the OLE DB provider does support distributed transactions, verify that the Microsoft Distributed Transaction Coordinator (MSDTC) is running.)
I think SQL Server is converting your operation with sp_executesql to distributed transaction in Oracle the above is what Microsoft says you need to do. Check number 9 in the link below.
http://support.microsoft.com/kb/280106
Kind regards,
Gift Peddie
February 23, 2007 at 7:59 am
Thanks for you input. I did run regedit to modify MSDTC settings but the behavior did not change. I must note that I could not configure exactly like the KB article. The Oracle server I am hitting is ver. 8.1 but I installed ver. 9.0 network utilities on the SQL Server. The "OracleSqlLib" registry entry value was set to "SQLLib80.dll" but I could not even locate the file on the server. So I tried "ORASQL9.dll" and "ORASQL8.dll" but neither changed the outcome. Clearly, "distributed transactions" and "MSDTC" are at the heart of my problem. I will keep investigating.
In the meantime, I can accomplish my goal by building the INSERT statement inside of the dynamic query (which contains the pass-through query). This works fine except that, without the use of sp_executesql parameters, I think the dynamic query gets recompiled at every execution. At least it works.
Thanks again.
February 23, 2007 at 8:54 am
Actually the download in the link below is what you need because 10g will not let you skip the installation of MTS in a Windows box. 8i is crappy a third party driver could help because in .NET Oracle ODP did not support 8i, Microsoft support 8i but not for most inserts.
http://www.oracle.com/technology/software/tech/windows/ole_db/htdocs/utilsoft.htm
Kind regards,
Gift Peddie
February 23, 2007 at 9:05 am
Wow! I have been looking for a download page like that for a long time. Very helpful, indeed. Thanks.
February 23, 2007 at 9:21 am
I am glad I could help, in the future don't search in Oracle site search in Google with what you need I do the same with MSDN2.
Kind regards,
Gift Peddie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply