SQL Server 2005 Linked Server to Oracle: TCP connection not closed
Posted on 2009-04-22
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 ?