July 14, 2008 at 5:26 pm
I have ODBC linked server connecting SqlServer 2005 to Oracle9i database. When the tables are queried via linked server, they show less records than the actual table. Any clue? I see similar posts on internet but not the solution.
Thanks in advance.
March 29, 2010 at 12:41 pm
This is an old post, but I just fought thru some of these issues, and it might help somebody. (Maybe even me in a few months when I've forgotten. 🙂 )
When you query the original tables directly, you know what database you are in.
When you query via the linked server, you are where the linked server points you to. Double check (maybe triple check) that it is pointing to the same machine. If you are connecting via a driver that uses a configuration file (as in an Oracle tnsnames.ora file), make sure that the driver file the linked server machine is using, and the driver file you use when connecting directly match.
Second, do you actually mean "tables" when you say "tables"? Or could they actually be views.
Views can filter out data based upon who is querying them. When you query the view directly in the original tool, you are querying as "you". When you connect via a linked server, you are querying as whomever the linked server is configured to connect as. This might be "you" and it might be some other account. A good number of the sys schema views work this way by default.
Third, there might be a "max number of records to return" parameter that is set too low somewhere in this mix of components.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply