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

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
Who is Participating?
siufConnect With a Mentor Author Commented:
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.
Chris MConsulting - Technology ServicesCommented:
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.
siufAuthor Commented:
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.
>>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.
siufAuthor Commented:
>> 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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.