SQL Server 2005 Linked Server to Oracle: TCP connection not closed

Posted on 2009-04-22
Medium Priority
Last Modified: 2013-12-18
I defined a Linked Server from SQL Server 2005 to Oracle 10g, using MSDAORA as provider. Our TNSNAMES is maintained in an Oracle OID server, thus there is no TNSNAMES.ORA file, but an LDAP.ORA file pointing to the OID. Now I have a view of an Oracle table, and the Select from that view works fine (using Management Studio). Problem: SQL Server opens a TCP connection to the OID server, and never closes it, even after closing Management Studio. Every time a user starts a session (through Internet Explorer in general), and displays that view, a new connection is opened and never closed. By the end of a day, 800 connections are left opened. After 4-5 days, the stack is full and I have to restart MSSQL. Is there a way to close these connections after, say, 2 hours ?
Thank you
Question by:siuf
  • 3
LVL 12

Expert Comment

by:Chris M
ID: 24203682
In practice, it's the SQL server that initiates the connection to Oracle as required by any client depending on whther the link is required or not.
This means that there's kind of a double hop done when an SQL client connects to the Oracle server. The connection is requested by your client but the server initiates it to the Oracle server.
If the client closes, SQL server is also supposed to close the client's connection to the linked server. This is where the failure is, so it might be linked to the driver you're using or the client(s) you're using but not TNSNAMES.
May i know which driver you're using?
May i know if an oracle client on your database server also leaves a hanging session when it's closed?
If this fails, there's a parameter that your Oracle Dba can set so that the connections are closed after they have neen idle for a certain time, this might be a work-around but do not go for it yet.

Author Comment

ID: 24204215
Thank you for your feedback.
I'm using MSDAORA, version 131072 (as seen in the SQL Profiler trace). On Oracle, I see the ldap connection when I connect from my PC with sqlplus, and this connection disappears as soon as I quit sqlplus.
LVL 20

Expert Comment

ID: 24209485
>>Every time a user starts a session (through Internet Explorer in general),

So the user runs an application (?) of some sort in the web browser, which in turn connects to the database? Or is this a direct connection to the SQL server?

If it's a web app, check the app code to see that the connection is closed there.

Author Comment

ID: 24211917
>> So the user runs an application
Of course.
But the problem is the same if I just run a query on the SQL Server itself, using SQL Server Management Studio. Just <Select * from FAC..dbo.V_SITES>. So the problem is not the user application. (FAC is the Linked Server name and dbo.V_SITES is a view on a table in Oracle). This simple query checks on the Oracle OID server what FAC is, and THAT connection remains opened even after closing Management Studio. Yesterday I had about 1,100 such connections...

Accepted Solution

siuf earned 0 total points
ID: 24655620
Finally we moved the databases to SQL Server 2008, and we use the Oracle OLEDB provider (the MSDAORA is supported until Oracle 8 only, we have Oracle 10). The problem does not occur anymore.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

862 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