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, Commented Jan 28, 2013 at 8:19
@YvesR get data from oracle(remote server) to mssql ...hence i created linked server — Shaggy
– Shaggy, Commented Jan 28, 2013 at 8:22
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
– YvesR, Commented Jan 28, 2013 at 8:24
I installed Oracle Client..using it i have created my DSN ...and linked server — Shaggy
– Shaggy, Commented Jan 28, 2013 at 8:44
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
– YvesR, Commented Jan 28, 2013 at 8:50

knagaev · Accepted Answer · 2013-02-06 13:00:13Z

5

+25

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

edited Feb 6, 2013 at 13:00

answered Feb 1, 2013 at 7:05

knagaev

2,96518 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Shaggy Over a year ago

If i modify my Query to include DSN

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

i get only 1 row but total there are 5

Shaggy Over a year ago

I already have "Oracle Provider for OLE DB 11.2.0.3.0" Installed on my System. How can i incorporate it in my Select Query ?

knagaev Over a year ago

For installation of ODAC the instruction is here -> (oracle.com/technetwork/topics/dotnet/downloads/…). Query will be like "from openrowset('MSDAORA.1','INDIANBANK';'ags';'mypass','Select * From ATM_PROGNOSIS.IR_ATMMON_AGS')"

knagaev Over a year ago

In addition you can read this article support.microsoft.com/…

Shaggy Over a year ago

Error : The OLE DB provider "MSDAORA.1" has not been registered.

|

user2369330 · Accepted Answer · 2013-05-10 08:57:20Z

My solution of the same problem was

Set Numeric Settings parameter to US numeric in Oracle ODBC driver settings.

Quote from ODBC help, problematic stuff is underlined) Numeric Settings - Allows you to choose which numeric settings will be used to determine the decimal and group separator characters when receiving and returning numeric data that is bound as strings. This option allows you to choose Oracle NLS settings (the default setting), Microsoft default regional settings (to provide a way to mirror the Oracle OLE DB driver’s behavior for greater interoperability), or US numeric settings (which are necessary when using MS Access or DAO (Database Access Objects) in non-US environments).

Collectives™ on Stack Overflow

SQL to Oracle Linked Server Openquery retrieves single row

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related