We've been experiencing a frustrating issue with a linked server object in SQL Server 2008 R2 to an Oracle 9i database. Please have a look, I have tried to be as detailed as possible. Any advice is appreciated.
We have built a new 64-bit Windows 2008 R2 machine. We have installed SQL Server 2008 R2 on there and have setup a linked server to a 32bit 9i database using the following sql query:
The DSN "TEST_LINK" is using the 64 bit 184.108.40.206 ODAC driver and was setup using the 64 bit ODBC.exe found in c:\windows\system32
We can connect to the target using sqlplus. We can tnsping the target. The DSN can connect successfully during its test connection.
The target database in the linked server is a 32-bit Oracle 9i Database (220.127.116.11.0) and the host machine is 32-bit Windows 2003 R2 server.
A test connection is made to the target database in SQL 2008 - a test returns successful. I can utilise the link programmatically from sql server 2008 and return results from v$instance fine. The problem being experienced is when we attempt to use the SQL Server Management Studio object hierarchy explorer to expand the TABLES and VIEWS tree. Doing this results in what seems like a hang with the label changing to TABLES (expanding...) Given 10 minutes or so, a time out error occurs OR on some occasions - the tree actually expands (at around 10 minutes) showing all the tables.
Here are some of our findings:
1. We are able to successfully create and explore the target database (18.104.22.168) through a Link Server object using SQL Server 2005 on another machine.
2. We ran a trace on the target database and traced the session opened from SQL Server 2008. We ran a trace for a session opened from SQL Server 2005 server. The 2005 SQL Server session trace features an explain plan for the the query trigger by the object explorer, the 2008 SQL Server session trace does feature the same query but not an explain plan as the query does not complete.
3. We can programmatically access data over the link in SQL Server 2008, no problems here - no delays or errors.
4. The 2008 SQL Server's Linked Server object can access a 32-bit Oracle 22.214.171.124 database on a 32-bit Windows 2000 server without any problems or delays.
5. The alert log of the target database is error free, log rotation is all that is reported.
6. The ROWS_PROCESSED column in v$sql view in the target database increments VERY slow when monitoring the connection coming from this SQL 2008 server.
7. Lastly, the target database is possibly on a different network segment, the 2 afore mention SQL Servers (2005 and 2008) are in the same network segment.
We installed 32 and 64 bit 126.96.36.199 client tools on our new server.
We installed Oracle 32 and 64 bit ODAC Drivers (ODAC1120320.zip and ODAC1120320_x64.zip) on our new server as well and have created and recreated the DSN TEST_LINK using them as well.
I feel that this problem is related to the way in which the triggered explorer query is handled between SQL server and oracle via the driver. The same driver produces results with a similar database in a similar environment.
I have searched for a solution for the last few days and have found articles relating to problematic connections between 32 and 64 bit platforms, but none that address a slow connection.
I would appreciate any advice or ideas as I am not sure what else to do for the client but suggest they change to using a tool like SQL Developer to retrieve the information they need. If you require more information, I will gladly supply it.