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

Posted on 2009-04-22
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
    LVL 12

    Expert Comment

    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

    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

    >>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

    >> 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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    SQL Server 2012 express 24 21
    SQL 2005 - Memory Table Column Names 11 56
    Pl/SQL Query 31 36
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to recover a database from a user managed backup

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now