We help IT Professionals succeed at work.

Linked server vs MS-Access odbc Oracle table record count

JimData
JimData asked
on
Medium Priority
702 Views
Last Modified: 2012-06-21
Recently migrated to windows 2008 / sql 2008 and am connecting to Oracle  via a linked server

Noticed problems in the data results.  I did a test with MSAccess to link to a table and found 8000 rows, when using
select * from openquery(LinkedServerName,'select * from TableName')
I received only 7000 rows

What could be causing this?????

Comment
Watch Question

Most Valuable Expert 2014

Commented:
I have seen in some database applications that it only returns a certain number of rows if there is not a where clause (and sometimes even if there is).

Its the assumption that most times you only need a sample -- not all the data. Take a look at the Oracle settings and see if there is something like that in it.

Author

Commented:
There is actually a 99 row count difference in table.  This is not related to a record count limit.  I can set the where clause to reduce the record count and still give a difference.
Most Valuable Expert 2014

Commented:
I ran into an issue with a linked Sybase to SQL -- someone had entered a wildly bad date in a field, the SQL Server choked pulling it over until we corrected it.

Is it a consistent 99 records? Do they have null fields, bad dates, anything like that?

Author

Commented:
Yes.  Also, just changed the provider from the InstantClient/ODBC to OpenClient/Oracle Provider and it now works (all tables read the correct recordcount)


Most Valuable Expert 2014
Commented:
>> InstantClient/ODBC to OpenClient/Oracle Provider

I still have not found a better option than using the orignating databases client/provider.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Most Valuable Expert 2014

Commented:
Glad to be of assistance. May all your days get brighter and brighter.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.