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.

Any 'special' column datatypes in use there? Like LOBs, LONG, Raw or something like that? Does it work when you do projection only on 'good' columns then? i.e. SELECT id from ... or similar — Fabian Barney
– Fabian Barney, Commented Jun 17, 2011 at 16:02
No special column types, just varchar2 and date. I did try to select a specific column and still 0 rows. — Clint Davis
– Clint Davis, Commented Jun 17, 2011 at 16:44

Gary Myers · Accepted Answer · 2011-06-18 01:11:03Z

1

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.

answered Jun 18, 2011 at 1:11

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maziar Taheri · Accepted Answer · 2011-06-17 20:13:20Z

0

Try specifying the schema name.

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

answered Jun 17, 2011 at 20:13

Maziar Taheri

2,33822 silver badges29 bronze badges

Comments

Clint Davis · Accepted Answer · 2011-06-21 18:30:41Z

0

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.

answered Jun 21, 2011 at 18:30

Clint Davis

4511 gold badge12 silver badges29 bronze badges

Collectives™ on Stack Overflow

Linked server not getting rows from Oracle DB

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related