?
Solved

Linked server vs MS-Access odbc Oracle table record count

Posted on 2009-02-11
6
Medium Priority
?
684 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?????

0
Comment
Question by:JimData
  • 4
  • 2
6 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 23623020
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.
0
 

Author Comment

by:JimData
ID: 23623251
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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23623427
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?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

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


0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 23625444
>> InstantClient/ODBC to OpenClient/Oracle Provider

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

Expert Comment

by:Jim P.
ID: 23685084
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question