Linked server to oracle headaches

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked server to oracle headaches

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-11-09 : 05:34:29

Hi,
I have created a linked server to an oracle92 database using oebc for oracle.

when I do

SELECT *
FROM OPENQUERY(oralink,
'SELECT count(*) from table11 ')
GO
I recieve 1780 rows

but If I do

SELECT *
FROM OPENQUERY(oralink,
'SELECT * from table11 ')
GO
I only receive 1680 rows !

why would this be !

Kristen
Test

22859 Posts

Posted - 2006-11-09 : 06:24:47

Does Oracle rely on some table stat for COUNT(*) - rather than physically counting the rows?

If so maybe that can be "freshened" ?

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-09 : 06:37:54

Is it because the data it returns is too big ?

Instead of SELECT * in the second query, try only selecting single column and see what happens.

Also have you tried same thing using four-part name like this:

Select count(*) from oracledb..usr.table11

Note: oracledb and usr are only for demonstration, use real names instead of them.

Harsh Athalye
India.
"Nothing is Impossible"

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-11-09 : 07:01:43

hi, no unfortunately it is a bit stranger than that.

If I run select ID from table11 order by ID
I receive the 1st record (2), then miss out 100 then get all the rest ofthe records.

If I run select ID from table11 order by ID DESC
I receive the 1st record (7000), then miss out 100 then get all the rest of the records.

insane !
Is there some strange bug with connecting sql2000 to oracle9i using ODBC ?

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 04:25:20

Do you have any way of checking if the Oracle database is corrupted?

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-11-13 : 10:34:31

Hi,
If I open SQL+ for oracle I can successfully run the sql and it returns the correct number of rows.

It simply makes no sense !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources