I'm trying to setup a linked server on MS SQL 2008 R2 to an Oracle 11g server. I've got the Oracle client installed and sqlnet.ora, tnsnames.ora & listener.ora copied from the server and in place with the following values:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = slug.blue.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle.blue.local) ) )
(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = slug.blue.local)(PORT = 1521)) ) )
but I keep getting this error when creating a new linked server
The linked server has been created but failed a connecton test. Do you want to keep the linked server?
An exception occurred while executng a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message
"ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA." (Microsoft SQL Server, Error: 7303)
Can anyone spot any problems with my config?
Are there other ways to specify the SERVICE_NAME or does everything come from the .ora files in the oracle client?