Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

100-row limitation using Oracle Linked Server Expand / Collapse
Author
Message
Posted Thursday, August 7, 2008 5:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 6, 2012 9:30 AM
Points: 9, Visits: 33
Am having a problem pulling data from an Oracle 9i 64-bit database using OpenQuery. The setup:

exec sp_dropserver 'ORALINK', 'droplogins';

exec sp_addlinkedserver
@server = 'ORALINK'
,@srvproduct = 'Oracle'
,@provider = 'OraOLEDB.Oracle'
--,@provider = 'MSDAORA'
--,@provider = 'MSDAORA.1'
,@datasrc = 'ORAServer';

exec sp_addlinkedsrvlogin
@rmtsrvname = 'ORALINK'
,@useself = 'FALSE'
,@locallogin = NULL
,@rmtuser = 'oraclelogin'
,@rmtpassword = 'pwsd';

Of the above providers, 'MSDAORA' does not work at all.

With 'MSDAORA.1', I get a valid connection, but get errors trying to pull data.

'OraOLEDB.Oracle' gives a valid connection *and* lets me execute OpenQuery requests, *but* limits results to 100 rows. For example,

--SUCCEEDS:
select * from openquery(ORALINK, 'SELECT COUNT(*) FROM ORATABLE');
--SUCCEEDS:
select top 100 * from openquery(ORALINK, 'SELECT * FROM ORATABLE');
--FAILS:
select top 101 * from openquery(ORALINK, 'SELECT * FROM ORATABLE');

Error msg for the last of the above queries is:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORALINK" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "ORALINK".

100 rows doesn't cut it, as I need to pull tens or hundreds of thousands of rows from Oracle on a regular basis. I have tried the above queries on several different Oracle tables, with identical results.

Anybody have a clue?

Post #548758
Posted Monday, August 18, 2008 4:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 27, 2008 12:41 AM
Points: 11, Visits: 27
it is working with 100+ rows in my case.... have u tried without openquery?

select top 102 * from ORALINK..SCHEMA.ORATABLE
Post #554200
Posted Monday, August 18, 2008 3:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 6, 2012 9:30 AM
Points: 9, Visits: 33
At your suggestion, I tried this:

SELECT TOP 100 * FROM ORALINK..CATALOG.ORATABLE --SUCCEEDS
SELECT TOP 101 * FROM ORALINK..CATALOG.ORATABLE --FAILS

Alas, same issue. This is really strange. May have to revive experiments with MSDAORA.1 again, which was giving us a completely different set of error msgs. It just can't be that hard to pull data from Oracle into SQL 2005!

Thanks for your suggestion...
Post #554640
Posted Tuesday, August 19, 2008 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 23, 2008 5:03 AM
Points: 1, Visits: 4
You need to look at your odbc settings.

On the performance screen access the advanced screen.

On this screen you need to change the Large Objects Threshold.

Place a tick on Use blocking wsith a fetch row of 1.

It is the settings on this screen that will enable you to retrieve greater number of records.

Note that the number of records returned varies depending on the size of the data returned.
Post #555201
Posted Thursday, August 21, 2008 6:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:34 AM
Points: 49, Visits: 175
I had a similar problem. I'm not sure it is the same as yours because in my case, the failure would have occured at record 10, not record 11 (as indicated below).

What I have learned:
The MS Oracle OLEDB provider is dormant at Oracle 8. Oracle stopped providing MS with necessary info and that was that. There is no point in ever using the MS provider except (maybe) for oracle databases at or below version 8. All Oracle OLEDB providers are backwards compatible.

The Oracle OLEDB provider has a bug in processing values in numerical fields where the value ends with a zero. It has to do with mis-interpreting the precision of the column. Typically, Oracle 9 and 10 (at least) use a precision of 38 to imply an integer. However, if the value ends in any number of zeros (eg, 10, 100, 1000), the OraOLEDB provider thinks the values have different precision levels and aborts.

IF this is your problem, you must convert the column to varchar2 (oracleSpeak) within the openquery select statement. There are two ways to do this that I am aware of:
1. use the oracle specific version of CAST ie 'TO_CHAR(COLNAME) AS COLNAME.'
OR
2. multiply the column by 1 , ie 'COLNAME*1 AS COLNAME'

The later generates a much larger column width (100).

If you are are using SELECT INTO or INSERT, you may convert them back into numerics on the fly with CAST() or CONVERT() outside the openquery().

I have not tried the OLEDB provider for Oracle 11. It might not have this bug (but both 9 and 10 do).

I've used the Oracle ODBC driver with MSAccess and it converts these numeric fields to the ACCESS version of varchar without explicit transformation code.




Post #557005
Posted Tuesday, April 13, 2010 1:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 12:42 PM
Points: 77, Visits: 237
I am having Linked Server issues with Oracle too. In my case, I want to copy the complete table from Oracle to SQL Server 2005. It gives me an error message: [ServerName] retruned message "". If I perform the operation with TOP 10, I get the 10 records OK. If I try to add a WHERE to pick one of the records, it gives me the same error, even with the TOP 10 restriction.

I can use Access 2003 and pull the whole table. That could be a work-around, but it is ugly.

Anybody have any ideas?



Shalom!,

Michael Lee
Post #902716
Posted Tuesday, March 11, 2014 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 12:55 AM
Points: 1, Visits: 0
select * 103 from openquery (LINKEDSERVERNAME,'select * from TABLENAME')
Post #1549574
Posted Sunday, June 15, 2014 12:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 1,391, Visits: 6,351
Didn't know about the bug, glad you mentioned it.
100 is the default fetchsize for the Oracle Client, you might increase it through the registry/provider string
Post #1580943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse