SQL to Oracle Linked Server Openquery retrieves single row

Question

I was using OpenQuery To get row set from Oracle table into my SQL Server. Then i find there is something known as OPENROWSET to fetch all rows

but it didnt worked for me.

SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=INDIANBANK;UID=ags;PWD=mypass',
   'Select * From ATM_PROGNOSIS.IR_ATMMON_AGS') AS a

Error :-

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Name of My DSN : INDIANBANK

I have used Microsoft OLE DB Provider for ODBC Driver To Create Linked Server to connect to oracle (ORACLE DB IS LOCATED ON REMOTE SERVER)

My DSN Show Test Connection Succeeded . Same as for My Linked Server.

When i execute following query Select * From openquery(IndianBank,'Select * From ATM_PROGNOSIS.IR_ATMMON_AGS') It retrieve only one row

My Linked SERVER CODE :-

EXEC master.dbo.sp_addlinkedserver @server = N'INDIANBANK', @srvproduct=N'IndianBankOracle', @provider=N'MSDASQL', @datasrc=N'INDIANBANK'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'INDIANBANK',@useself=N'False',@locallogin=NULL,@rmtuser=N'ags',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INDIANBANK', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Your text is a bit confusing (at least to me), you want to get data from oracle to mssql or otherwise? — YvesR
@YvesR get data from oracle(remote server) to mssql ...hence i created linked server — Shaggy
I never was able to get data from oracle without installing the oracle client. After I created a connection configuring the TNSNAME. Then I was able to access via ms sql, script, etc. . The ODBC oracle driver from Microsoft that is shipped with the system don't work correct as far as I know, so you tried the other way I described? — YvesR
I installed Oracle Client..using it i have created my DSN ...and linked server — Shaggy
Oh you said MS oracle driver, my bad then. Can you test to open a "Data import wizward" from MS SQL, connect the oracle database and use the same query, there you can check if you get a correct connection and retrieve all data. So you can be sure driver, etc. is all ok and only linked server configration left. — YvesR

knagaev knagaev · Accepted Answer · 2013-02-01T07:05:29

If you used the string like this "'MSDASQL','DRIVER={SQL Server};SERVER=INDIANBANK;UID=ags;PWD=mypass', 'Select * From ATM_PROGNOSIS.IR_ATMMON_AGS'" you got DSN-less connection so tests of DSN are useless.

I recommend you to take this driver
Oracle Data Provider for .NET
It's much more friendly with Oracle.

Here is
the example for ODP.NET
where tuning is described.

And here is the full docs for ODP.NET

Hope this helps :)

See my comments below.

P.S. For Microsoft driver the parameters are shown here Microsoft OLE DB Provider for ODBC

SQL to Oracle Linked Server Openquery retrieves single row

2 Answers