Re: Connection pooling issue with pass through queries

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Bill E.
    #1

    Re: Connection pooling issue with pass through queries

    Rick,

    That's almost like asking why we should bother with database
    connection pooling. It's a question of efficient use of resources and
    avoiding problems that may occur in production. I was one single user
    clicking through one single form and opened over 20 connections.
    That's just plain sloppy and unacceptable. Who knows how many
    connections would be opened with many users working on a complete
    application in extended sessions. It wasn't long ago that I had to
    fix someone else's web application because it opened hundreds of
    connections and stopped all users in their tracks when the number of
    allowable connections was exceeded.

    The problem is eliminated when I set the connection string to use a
    SQL Server login instead of a trusted connection. Until I understand
    what's going on here, I'm going to stick with that. Any insight into
    what may be causing this would be welcomed.

    Thanks,

    Bill E.
    Hollywood, FL
    Tags: None
    • Rick Brandt
      #2
      Re: Connection pooling issue with pass through queries

      Bill E. wrote:
      Rick,
      >
      That's almost like asking why we should bother with database
      connection pooling. It's a question of efficient use of resources and
      avoiding problems that may occur in production. I was one single user
      clicking through one single form and opened over 20 connections.
      That's just plain sloppy and unacceptable. Who knows how many
      connections would be opened with many users working on a complete
      application in extended sessions. It wasn't long ago that I had to
      fix someone else's web application because it opened hundreds of
      connections and stopped all users in their tracks when the number of
      allowable connections was exceeded.
      >
      The problem is eliminated when I set the connection string to use a
      SQL Server login instead of a trusted connection. Until I understand
      what's going on here, I'm going to stick with that. Any insight into
      what may be causing this would be welcomed.
      I can only tell you that I have been using ODBC with trusted connections
      with around 200 users for over a decade and have never once had an issue
      with it.

      Connections ARE re-used.

      --
      Rick Brandt, Microsoft Access MVP
      Email (as appropriate) to...
      RBrandt at Hunter dot com


        Comment

        • Erland Sommarskog
          #3
          Re: Connection pooling issue with pass through queries

          Bill E. (billmiami2@net scape.net) writes:
          That's almost like asking why we should bother with database
          connection pooling. It's a question of efficient use of resources and
          avoiding problems that may occur in production. I was one single user
          clicking through one single form and opened over 20 connections.
          That's just plain sloppy and unacceptable. Who knows how many
          connections would be opened with many users working on a complete
          application in extended sessions. It wasn't long ago that I had to
          fix someone else's web application because it opened hundreds of
          connections and stopped all users in their tracks when the number of
          allowable connections was exceeded.
          >
          The problem is eliminated when I set the connection string to use a
          SQL Server login instead of a trusted connection. Until I understand
          what's going on here, I'm going to stick with that. Any insight into
          what may be causing this would be welcomed.
          I have no idea of what might be going on. I can understand that
          connections are not being resued - the typical reason is that they
          are not closed properly. But why it would work with SQL authentication
          and not Windows authentication, I don't know.

          My guess it that when you changed the authentication, you changed something
          more thar you did not tell us about.

          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

            Comment

            • Bill E.
              #4
              Re: Connection pooling issue with pass through queries

              Thanks for your comments Lyle, Mary and Erland.

              Lyle -- I'm not sure how much Jet is involved in this because I have
              no queries that need to be translated. Everything is a pass through
              to SQL Server. There are no linked tables in my application. I've
              seen that technique you mentioned for the ADO connection, but I'm not
              having any problems with ADO. It's the pass-through queries that are
              the problem.

              Mary--Thanks for the resources. The first article deals primarily
              with querying SQL Server through linked tables so does not apply to my
              case. I'm interested in seeing what the ODBC trace looks like and I'm
              hoping that it will not simply be an academic exercise but will lead
              me to some kind of corrective action.

              Erland -- I changed the connection string only, nothing else.

              Bill

                Comment

                Previous template Next
                Working...