August 7, 2008 at 5:37 pm
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?
August 18, 2008 at 4:47 am
it is working with 100+ rows in my case.... have u tried without openquery?
select top 102 * from ORALINK..SCHEMA.ORATABLE
August 18, 2008 at 3:44 pm
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...
August 19, 2008 at 11:03 am
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.
August 21, 2008 at 6:04 pm
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.
April 13, 2010 at 1:42 pm
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?
Michael Lee
March 11, 2014 at 12:57 am
select * 103 from openquery (LINKEDSERVERNAME,'select * from TABLENAME')
June 15, 2014 at 12:21 pm
Didn't know about the bug, glad you mentioned it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply