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=220.127.116.11) (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.