Conference orarep::nomahs::sql_services

2193.0. "Second class server starts for same client?" by ORAREP::AWECIM::HOWARD () Thu Apr 17 1997 15:45

    I have an MS-Access database that contains a few local tables as well
    as links to 6 Rdb tables.

    Before I open any of the linked tables, SQL report generates:


      Class Name        Startup          Username         Min   Max   Idle
      ================  ===============  ===============  ====  ====  =====
      ERSDB             SQLSRV$SRV61     ERSDBSQLU        3     20    1800

        PID       Process Name     State      API Association
        ========  ===============  =========  ===============================
        24A07277  SS_ERSDB_0004    Idle       No Connection
        24A06A5D  SS_ERSDB_0003    Idle       No Connection
        24A05FB5  SS_ERSDB_0002    Idle       No Connection
        24A061B4  SS_ERSDB_0001    Idle       No Connection


    When I startup Access and open one of the smaller Rdb linked tables, the
    ODBC driver connect window asks for the username/password.  Access is
    able to create the link and open the table.  I can further open a few
    more small tables.  I can see one API Association become active as shown
    here:

        PID       Process Name     State      API Association
        ========  ===============  =========  ==============================
        24A07277  SS_ERSDB_0004    Active     TCP/IP 16.127.192.70 ERSDBSQLU
        24A06A5D  SS_ERSDB_0003    Idle       No Connection
        24A05FB5  SS_ERSDB_0002    Idle       No Connection
        24A061B4  SS_ERSDB_0001    Idle       No Connection


    While still running Access, if I open a larger table (consisting of 
    hundreds of records) in the linked Rdb database, SQL Services seems to 
    startup a second class server as shown here:

        PID       Process Name     State      API Association
        ========  ===============  =========  ===============================
        24A07277  SS_ERSDB_0004    Active     TCP/IP 16.127.192.70 ERSDBSQLU
        24A06A5D  SS_ERSDB_0003    Active     TCP/IP 16.127.192.70 ERSDBSQLU
        24A05FB5  SS_ERSDB_0002    Idle       No Connection
        24A061B4  SS_ERSDB_0001    Idle       No Connection

    As expected, I was not prompted by the ODBC driver connect window for 
    the username/password.

    Why is a second class server started up?  How can I avoid this?  We cannot
    afford to set MAX to 100 to support our 50 PC clients (the potential of 
    100 processes running on the node is of concern, given the number of
    interactive users).

    Is it possible that we have the resources for the SQLSRV$SRV61 or 
    ERSDBSQLU account set too low?

    SQLSRV$MAX_TCP_CONNECTIONS is set at 40.

    Thanks,
    John Howard
    Digital Semiconductor
    
    Software Versions
    -----------------
    PC:
        Windows NT 4.0
        MS-Access V7.00 32-bit
        ODBC RDB V2.10.11 32-bit with TCP/IP

    OpenVMS:
        DEC SQL V6.1-02
        DEC Rdb V6.1-0
        Rdb/Dispatch V6.1-0 (OpenVMS AXP)

does note 324 in the odbc conference apply?

It is most likely Access doing it.

Thanks Vic.

I have a follow-up question.

Here's the note (from 1993...good memory!) you mentioned:

================================================================================
Note 324.1        Multiple table attaches -> multiple servers?            1 of 1
BROKE::MESENZEFF                                     15 lines   5-NOV-1993 16:56
--------------------------------------------------------------------------------
    
    MS Access determines how many links to create. You need to be using
    MS Access V1.1 (no field test versions), and either V1.0 or T1.1 of the 
    DEC ODBC Driver (no T1.0 field test versions). MS Access will create a new
    link everytime you choose the "attach table..." option from the FILE menu. 
 
    If you choose the "attach table..." option once. You will get one
    server. You can then attach each table in the list one at a time. All of 
    this will happen using the same server. 
    
    When you open the FIRST table that contains a unique index MS Access will
    create a second link.  
    
    
    Vic



I tried an experiment with some Visual Basic code connecting to the same
remote tables, avoiding MS-Access entirely.  I saw the same behaviour as 
stated in .0, so I will assume that this is a Microsoft Jet database engine
behavior that is used by MS-Access and Visual Basic.

That's too bad, because it means that I'm stuck with this behaviour for any 
tables containing a unique index.  Can anyone suggest a workaround that 
avoids raw ODBC API calls? 
    
Why should a unique index require a new class server to startup?
    
-John

    This is how MS handles DBs that close cursors on commit. They go in
    with a read only and a read write transaction in an effort to reduce
    locking. 
    
    In RDB7, there is an option called HOLD CURSORS that allows rdb to
    leave cursors open across commits. To use this, you need
    RDB7/SQLSRV7/ODBC 2.1. This should help in that Access will handle
    connections differently. 
    
    Also, in SQLSRV7, you can have multiple clients connected to 1 DB
    executor (process). This is called transaction reusable servers and for
    this to work effectively, your transactions must be short. 
    
    Look in the RDB7 and SQLSRV7 doc for some of the gotchas for either one
    of these methods. 
    
    Of course, this really has nothing to do with ODBC per se because the
    ODBC driver is just doing what MS Access/VB told it to. For better
    control over your connections and a good performance increase, native
    ODBC is the way to go...
    
    john