5

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
Shaggy
  • 5,422
  • 28
  • 98
  • 163
  • Your text is a bit confusing (at least to me), you want to get data from oracle to mssql or otherwise? – YvesR Jan 28 '13 at 08:19
  • @YvesR get data from oracle(remote server) to mssql ...hence i created linked server – Shaggy Jan 28 '13 at 08: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 Jan 28 '13 at 08:24
  • I installed Oracle Client..using it i have created my DSN ...and linked server – Shaggy Jan 28 '13 at 08: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 Jan 28 '13 at 08:50
  • I guess u didnt get my question ..i m facing error here `SELECT a.* FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=INDIANBANK;UID=ags;PWD=mypass', 'Select * From ATM_PROGNOSIS.IR_ATMMON_AGS') AS a` – Shaggy Jan 28 '13 at 08:55
  • I understood, all I mean is to manually try the data import assitant and check if 'Select * From ATM_PROGNOSIS.IR_ATMMON_AGS' returns all rows there. – YvesR Jan 28 '13 at 10:18
  • I Have used Oracle SQL Developer and there i connect to Oracle Database ...it retrieves all rows there. – Shaggy Jan 30 '13 at 10:22
  • Please review this link, it might just have the answer you are looking for... http://www.sqlservercentral.com/Forums/Topic548758-1042-1.aspx – SQLGuru Jan 30 '13 at 12:45

2 Answers2

5

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

knagaev
  • 2,897
  • 16
  • 20
  • 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 Feb 01 '13 at 12:37
  • 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 ? – Shaggy Feb 01 '13 at 12:42
  • For installation of ODAC the instruction is here -> (http://www.oracle.com/technetwork/topics/dotnet/downloads/install112030-1440546.html). Query will be like "from openrowset('MSDAORA.1','INDIANBANK';'ags';'mypass','Select * From ATM_PROGNOSIS.IR_ATMMON_AGS')" – knagaev Feb 06 '13 at 09:49
  • In addition you can read this article http://support.microsoft.com/default.aspx?scid=kb;en-us;280106&Product=sql – knagaev Feb 06 '13 at 09:56
  • Error : `The OLE DB provider "MSDAORA.1" has not been registered.` – Shaggy Feb 07 '13 at 09:21
  • If you use an x64 system please try "OraOLEDB.Oracle" instead of "MSDAORA.1" (this is only for x86). – knagaev Feb 07 '13 at 13:24
  • Error: `OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".` AS i am able to see `OraOLEDB.Oracle` Provider Under Linked Server Tab – Shaggy Feb 07 '13 at 13:46
  • Did you try to use "from openrowset('OraOLEDB.Oracle','INDIANBANK';'ags';'mypass','Select * From ATM_PROGNOSIS.IR_ATMMON_AGS')"? – knagaev Feb 07 '13 at 14:25
  • @SagarDumbre You didn't create TNS alias. Please read this link http://docs.oracle.com/cd/E11882_01/win.112/e23174/featConnecting.htm#i1006228 By the way you can connect without the use of the tnsnames.ora file - search for "Using the Connect Descriptor" in the article. – knagaev Feb 08 '13 at 06:27
  • now this also doesnt work. `Select * from openrowset('OraOLEDB.Oracle','INDIANBANK','Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SOME_NAME)));User Id=ags;Password=mypass;','Select * From ATM_PROGNOSIS.IR_ATMMON_AGS')` – Shaggy Feb 08 '13 at 12:22
  • You have to edit this string with your parameters. Select * from openrowset('OraOLEDB.Oracle','user id=ags;password=mypass;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=INDIANBANK)(PORT=1521‌​)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SOME_NAME)))','Select * From ATM_PROGNOSIS.IR_ATMMON_AGS'). Change SOME_NAME with your real service name. – knagaev Feb 08 '13 at 12:28
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24190/discussion-between-sagar-dumbre-and-knagaev) – Shaggy Feb 08 '13 at 13:21
0

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).

user2369330
  • 1