SQL Server Linked server query problem (group by)

Question

I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well.

I am trying to migrate to SQL Server 2012 via SQL Server 2008.

I am encountering some problems with the linked Oracle servers on SQL Server 2008 and SQL Server 2012.

I downloaded the last Oracle 10 Instant Client since we have some older Oracle databases I need access to, and have configured tns_admin, and checked with SQL Plus; all good so far.

I created an ODBC system DSN to point to one of the Oracle databases. I then created my linked server using that DSN.

The linked server returns the standard date query, select sysdate from dual, just fine, and will return the count of specific queries too. However, when I try to return the actual rows from a table, I get a smaller number than I should.

Also, if I run an aggregate GROUP-BY query, I only get one row returned!

I would have tried downloading the full client from Oracle, but in their infinite wisdom, the v10 client is no longer available!

Any help or advice would be greatly appreciated.

• user3629: Why not use the 11.2 client or ODAC? They work with a 10g database and work correctly with 2008 R2 and 2012 link servers

Answer 1

The latest full client will work fine with 10g. I have experienced similar problems using anything other than the full client, even though according to Oracle the others should work. Just make sure that you completely uninstall all elements of the previous installs as having multiple clients/versions can create another set of problems.