2

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

Improve this question
3
  • 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, 2009 at 21:50
  • And are you able to use sqlplus from the server to connect?
    – Sam
    Nov 17, 2009 at 23:58
  • It is 32 bit for now. I can also connect with sqlplus.
    – Dwight T
    Nov 18, 2009 at 13:42

2 Answers 2

Reset to default
1

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.

Improve this answer
1

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

Improve this answer
1
  • Sounds like your sqlnet.ora and tnsnames.ora may have been different on that other machine.
    – Doug Porter
    Dec 4, 2009 at 19:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .