Wednesday, December 5, 2012

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server Oracle

We may encounter the below error while setup linked server from SQL Server to Oracle
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server Oracle

Steps to troubleshoot

1. Verify the DNS using ODBC from Administrative tools by using Oracle Client Driver.
2. If DNS test connection is succeds then we need to setup linked server from SQL Server
3. SSMS - Server Objects - Linked Servers  -- New Linked Server
4. Linked Server -- OracleLinkServer
5. Select Other Data Source
6. Provider -- Select Microsoft OLEDB Provider for ODBC Drivers
7. Product Name -- Specify Oracle
8. Data Source -- Specify DSN which we created earlier
9. On Security (at left pane) Select Be made using the security context
10. Specify Oracle user name and password
11.On Server Options, make sure Data Access, RPC and RPC Out as True.
12. Finally hit OK

Hope that will fix the issue if oracle client components are installed on the machine.

To Query Linked Server

Select * from OpenQuery(linkedservername, 'query')

Select * from openquery(oraclelinkserver, 'select * from test')

Insert into openquery (oraclelinkserver,'select name, designation from test') values 'kalyan', 'dba'

Insert into openquery(oraclelinkeserver, 'select name, designation from test') select name, designation from test (sql server table)

delete from openquery(oraclelinkserver, 'delete from test where name =''kalyan''');

update openquery(oraclelinkserver,'select name from test where id=1') set name='kalyan kumar';

 

No comments:

Post a Comment