SQL Server Linked Server to Oracle Fails with Cannot Initialize Data Source Object error
You have a running SQL Server and you need to create a linked server to Oracle.
You follow these steps
Install Oracle client in the SQL Server
Setup tnsnames.ora and sqlnet.ora files
Test connection to Oracle using tnsping and sqlplus
Add Linked Server
Now when you test it, you get a long error message saying
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORATEST"
OLEDB provider "OraOLEDB.Oracle" for linked server "ORATEST" returned message "ORA-12154: TNS: Could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)
This has been an issue with Linked Server to Oracle from the beginning of time. SQL Server does not see Oracle driver after you install it until you restart the SQL Server.
Restart your SQL Server service your linked server will start working.
You have a running SQL Server and you need to create a linked server to Oracle.
You follow these steps
Install Oracle client in the SQL Server
Setup tnsnames.ora and sqlnet.ora files
Test connection to Oracle using tnsping and sqlplus
Add Linked Server
Now when you test it, you get a long error message saying
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORATEST"
OLEDB provider "OraOLEDB.Oracle" for linked server "ORATEST" returned message "ORA-12154: TNS: Could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)
This has been an issue with Linked Server to Oracle from the beginning of time. SQL Server does not see Oracle driver after you install it until you restart the SQL Server.
Restart your SQL Server service your linked server will start working.