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

Dwight T
  • 209
  • 2
  • 7
  • 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 Answers2

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.

squillman
  • 37,618
  • 10
  • 90
  • 145
1

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

Dwight T
  • 209
  • 2
  • 7