June 25, 2009 at 5:15 am
Hi,
We're setting up a linked server to copy data from an Oracle system to a SQL Server one. Using SQL Profiler (very nice tool), I capture the following (db names, usernames and pwds changed), with a much simplified select statement in the OpenQuery:
EXEC sp_addlinkedserver 'linkedServerName','Oracle','MSDAORA','oracleDBNameFromTNSNames';
go
EXEC sp_addlinkedsrvlogin 'linkedServerName', 'FALSE',NULL, 'oracleUserName', 'oraclePassword';
go
create database targetSQLDatabase;
go
SELECT * INTO [targetSQLDatabase]..[ourTable] FROM OpenQuery(linkedServerName,'select * from ourTable' ) ;
go
This fails at the OpenQuery complaining that the oracle client is not installed...but it is. I can connect to and peruse the oracle db from the sql server box. If, however, I change 'MSDAORA' to 'OraOLEDB.Oracle' it all just works. On development and some test machines, the MSDAORA provider works as well. This happens on both sql 2005 and 2008. The oracle db is running on 10g.
I've spent a lot of time now looking for answers to this on the web and have only just realised that SqlServerCentral has an Oracle forum. Lots of people appear to be having this problem but no-one has provided an answer that works for us. I'm hoping that someone here will have hit and solved this problem.
Thanks in advance.
July 10, 2009 at 2:05 am
Sorted it!
The problem was the following:
1) The Server had Oracle 10 and 11g clients installed.
2) MSDAORA appears to not work with 11g client.
3) The 11g client paths were earlier in the PATH environment variable than the 10 client paths.
So...MSDAORA was calling the Oracle client, getting the 11g client and trying to use it. It failed. We moved the 10g client paths to just in front of the 11g ones in PATH - and the code started working. We then restored the PATH to its previous value and the code failed. This move/restore was repeatable and showed predictable results.
July 10, 2009 at 11:36 am
:w00t: wow! Thank you for sharing. Very good catch, congrats.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply