Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to create a link server from SQL Server to Oracle, but I'm having no luck.

I've install the oracle 10g client software and I'm able to use TOAD for oracle and use ODBC to connect to the database directly. When I try to create a link server using the MS oracle driver, I get the following error when I try to select from the link server.

ORA-12154: TNS:could not resolve the connect identifier specified

This is what I use to create the link server, MAGOO is the entry in the TNSNames file.

EXEC sp_addlinkedserver 'MagooLink', 'Oracle', 'MSDAORA', 'MAGOO'

Using the ODBC provider I get a different error stating MSDASQL reported an error.

My TNSName file looks like this.

MAGOO=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=12.196.15.82)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=magoo.something.com)
    )
  )

Any direction would be helpful. I've scanned quite few resources on the net and nothing seems to help.

Dwight

share|improve this question
    
64bit or 32bit Windows ? I've got ODBC working on a 32-bit windows to Oracle without problem, but always have problem with 64 bit windows (or maybe it is with 32-bit clients on 64-bt windows). –  Gary Nov 17 '09 at 21:50
    
And are you able to use sqlplus from the server to connect? –  Sam Nov 17 '09 at 23:58
    
It is 32 bit for now. I can also connect with sqlplus. –  Dwight T Nov 18 '09 at 13:42

2 Answers 2

We've had heartburn trying to setup linked servers from 64-bit SQL to Oracle, although never had any problems on 32-bit. Seems that MS and Oracle don't like playing together...

We have them working using the Oracle Provider for OLE DB (OraOLEDB.Oracle). We also have Allow Inprocess and Non Transacted Updates set to true on the provider.

share|improve this answer

I was able to get it to work on another machine using the microsoft oracle driver.

share|improve this answer
    
Sounds like your sqlnet.ora and tnsnames.ora may have been different on that other machine. –  Dougman Dec 4 '09 at 19:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.