ADO OLEDB connection to MSSQL: pooling not working

From: Thomas Holme (thomas_at_hampen.com)
Date: 02/16/05

  • Next message: Zhuo: "size limit of BLOB field" 
    Date: Wed, 16 Feb 2005 14:44:44 +0100

    Hi there,

    I am using WinXPSp2, D7, ADO OLEDB to connect to MSSQL8.0.

    I have a problem with the OLE DB Resource Pooling.

    -----------------

    I create a form with

         Connection1: TADOConnection;
         Table1: TADOTable;
         DataSource1: TDataSource;
         DBGrid1: TDBGrid;
         Button1: TButton;
         Button2: TButton;

    and write the following code:

    procedure TForm1.Button1Click(Sender: TObject);
    begin
       Table1.Active:=not Table1.Active;
    end;

    procedure TForm1.Button2Click(Sender: TObject);
    var
       i: integer;
    begin
       for i:=0 to 9 do
         Button1Click(nil);
    end;

    procedure TForm1.FormCreate(Sender: TObject);
    begin
       Connection1.Connected:=true;
    end;

    My connectionstring looks like this:

    Provider=SQLOLEDB.1;
    Password=MyPassword;
    Persist Security Info=False;
    User ID=MyUID;
    Initial Catalog=MyCat;
    Data Source=MyDB;
    Network Library=DBMSSOCN;
    Use Procedure for Prepare=1;
    Auto Translate=True;
    Packet Size=4096;
    Workstation ID=MyPC;
    Use Encryption for Data=False;
    Tag with column collation when possible=False

    -----------------

    Running this works almost as intended. The connection opena a port and
    data appears in the grid as planned.

    When I close the Table (but not the connection) the port used for the
    connection enters a state called "TIME_WAIT". Here (in the resource
    pool) it remains for 4 minutes, then it is flushed and goes away.

    But if I open the table before the timeout of 4 mins, I get a new
    connection. For some reason, I dont get the connection in the pool.

    On my simple test system this is no problem, but when heavy load is
    applied it kills my application.

    I have read

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

    several times, but I cant seem to get it to work.

    Please help.
    /TK

  • Next message: Zhuo: "size limit of BLOB field"