Linked server not getting rows from Oracle DB

Question

We are using SQL Server 2008 R2 Standard and linked servers to get information from other Oracle DB's. The provider for our linked servers is the Oracle Provider for OLE DB.

I have this query that I try to run in SQL Server.

 select * from [HPROD]..[GTBUD].GT_BUD_COA_VW

Using Oracle SQL Developer I can see that there are 44,859 records in the table. Any ideas why I can't get the data? I should point out that the HPROD linked server, the one I'm having trouble with is running Oracle 11g 64-bit 11.2.0.1.0 Production. Our SQL Server is running on a Server 2008 R2 64 box.

The interesting this is we have another linked server called IRPROD using the same provider as HPROD. IRPROD is running the exact same version of Oracle but we have no problems with it. Everything runs fine.

Also, if I try to use OpenQuery, HPROD still will not show any rows.

 select * from Openquery(HPROD,'select * from GTBUD.GT_BUD_COA_VW')

I'm at a loss. We need to be able to hit these tables.

Answer 1

I worked with my coworker who actually created the view.

The view had joins and where clauses. He was using a syntax like this:

 AND b.end_dt = '30-JUN-2012'

When he changed the format to use a todate function it worked.

 AND b.end_dt = TO_DATE('06/30/2012','MM/DD/YYYY')

Now the old way worked perfectly fine in Oracle, it only barfed when being passed through the linked server in SQL Server.

Now we know.

Answer 2

Verify that the linked server is pointing to the database (and login username/schema) that you expect.

On the Oracle side, you could check v$session to see that you have a connection from the SQL Server node, and v$sql to see what query was issued, how many fetches were done and how many rows returned.

Answer 3

Try specifying the schema name.

select * from
[linkedServerName].[DatabaseName].[SchemaName].[TableName]