Error/Fix: Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server. (.Net SqlClient Data Provider)
Recently I was working on one user request to create a linked server for Oracle database in SQL Server 2014 instance.
After installing the correct Oracle ODBC drivers, when I tried to expand the Linked server node to browse objects, I was greeted with below error:-
Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “ORACLE_DR”. (.Net SqlClient Data Provider)
Resolution:-
In order to fix the issue, I simply needed to add the connection string provided by user in TNSNAMES.ORA file.
To find the exact location of ORA file, first check for ORACLE_HOME path. In many cases, the System PATH variable itself can provide the location of Oracle client binaries. To get this information, the easy method could be to use Powershell.
1 |
dir env: |
Once base Drive is identified, like in my case, it is F:\ drive for 1st Oracle binaries path, then I simply needed to find TNSNAMES.ORA file closest to the above binary path. For finding this ORA file, below Powershell command can be used:-
1 |
Get-ChildItem -Path f:\ -Recurse -Name *TNSNAMES.ORA* |
In short, I added the connection string provided by the user in file F:\app\oracle11g\network\admin\TNSNAMES.ORA. In some cases, we have to reboot SQL Server services as well.
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂
Related
Related Posts
-
Error/Fix: Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation
No Comments | Apr 15, 2018
-
Performance Tuning With Predicate Logic
No Comments | Oct 15, 2016
-
Easy Trick to Debug Long TSQL Codes
No Comments | May 6, 2017
-
Error/Fix: Database cannot be upgraded because it is read-only or has read-only files
No Comments | Jul 26, 2020
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.