CODEWITHSUNDEEP
t-sql
Bill
Bill

Reputation: 135

SQL: OPENQUERY Not returning all rows

i have the following which queries a linked server i have to talk to.

SELECT * FROM

OPENQUERY(DWH_LINK, 'SELECT * FROM TABLEA ')

It will typically return most of the data but some rows are missing?

The linkeds server is coming from an oracle client

Is this a problem anyone has encountered w/ openquery?

Upvotes: 5

Views: 11972

Answers (4)

Matt Roy
Matt Roy

Reputation: 1525

I had exact same problem with an SQL 2014 getting data from SQL 2000 through OPENQUERY. Because ODBC compatibility problem, I had to keep generic OLE DB for ODBC driver. Moreover, the problem was only with SQL non-admin account. So finally, the solution I found was to add SET ROWCOUNT 0:

SELECT * FROM OPENQUERY(DWH_LINK, 'SET ROWCOUNT 0 SELECT * FROM TABLEA ')

It seems the rowcount might been change somewhere through the SQL procedure (or for this user session), so setting it to 0 force it to return "all rows".

Upvotes: 0

Ken
Ken

Reputation: 349

I had this same problem using the Oracle 10 instant client and ODBC. I used this client as I am connecting to an Oracle 10gR2 database. I opened a ticket with Microsoft support and they suggested using the Oracle 11 instant client. Surprise! Uninstalling the 10g instant client, installing the 11g instant client and rebooting resolved the issue.

Ken

Upvotes: 0

Henry
Henry

Reputation: 69

I had exactly the same problem.

The root cause is that you've set up your linked server using ODBC instead of OLE DB.

Here's how I fixed it:

  • Delete the linked server from SQL Server
  • Right click on the "Linked Servers" folder and select "New Linked Server..."
  • Linked Server: enter anything..this will be the name of your new linked server
    • Provider: Select "Oracle Provider for OLE DB"
    • Product Name: enter "Oracle" (without the double quotes)
    • Data Source: enter the alias from your TNSNAMES.ORA file. In my case, it was "ABC.WORLD" (without the double quotes)
    • Provider String: leave it blank
    • Location: leave it blank
    • Catalog: leave it blank

Now go to the "Security" tab, and click the last radio button that says "Be made using this security context:" and enter the username & password for your connection

That should be it!

Upvotes: 6

MikeCov
MikeCov

Reputation: 136

This seems to be related to the underlying provider capabilities and others have also run into this and similar size/row limitations. One possible work-around would be to implement an iterative/looping query with some filtering built in to pull back a certain amount of rows. With oracle, I think this might be using the rownum (not very familiar with oracle).

So something like 

--Not tested sql, just winging it syntax-wise
SELECT * FROM OPENQUERY(DWH_LINK, 'SELECT * FROM TABLEA where rownum between 0 AND 500')
SELECT * FROM OPENQUERY(DWH_LINK, 'SELECT * FROM TABLEA where rownum between 500 AND 1000')
SELECT * FROM OPENQUERY(DWH_LINK, 'SELECT * FROM TABLEA where rownum ...')

BOL: link This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Upvotes: 1

Related Questions