SQL Server 2008 Linked server to Oracle 9i

Question

I found:

http://stackoverflow.com/questions/307636/how-do-you-setup-a-linked-server-to-an-oracle-database-on-sql-2000-2005

The DSN tested successfully but after using the stored procedures outlined in the article to create the linked server the 'open query' returned the following message:

OLE DB provider "MSDASQL" for linked server "XXX" returned message "Specified driver could not be loaded due to system error 1114 (Oracle in instantclient11_1).".

Does something need to done differently on MSSQL 2008? I was successful with MSSQL 2000.

I tried using the GUI to create the LS but received the error where it could not get the error message.

I am using instant client 11_1 on Windows server 2003.

Answer 1

You shouldn't be using the MSDASQL as the provider you should be using:

@provider=N'MSDAORA'

or

@provider=N'OraOLEDB.Oracle'

At least this is how my Oracle linked server is configured on every database server I have linked to Oracle. Some have the Oracle native from installing a complete Administrative Client setup, others have thin client. I don't have a DSN setup in ODBC on any of them. If you can TNSPING the Oracle instance from the command prompt on the server, then you should be able to connect to it from SQL with a linked server.