SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


100-row limitation using Oracle Linked Server


100-row limitation using Oracle Linked Server

Author
Message
Bonz99
Bonz99
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 41
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?
upalsen
upalsen
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 27
it is working with 100+ rows in my case.... have u tried without openquery?

select top 102 * from ORALINK..SCHEMA.ORATABLE
Bonz99
Bonz99
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 41
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...
julian pierre
julian pierre
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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.
Scott MacCready
Scott MacCready
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 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.



Michael Lee-169622
Michael Lee-169622
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 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
guruprakashc
guruprakashc
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 0
select * 103 from openquery (LINKEDSERVERNAME,'select * from TABLENAME')
Jo Pattyn
Jo Pattyn
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11791 Visits: 10188
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search