SQL: OPENQUERY Not returning all rows

Question

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?

Answer 1

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!