SQL Server Performance

sp_reset_connection and dormant connections.

Discussion in 'General Developer Questions' started by EsioN, Jan 24, 2005.

  1. EsioN New Member

    My problem:
    Server01 (SQL Server 2000) and Server02 (SQL Server 2000) are diferent machines, but need talk together.
    I have some procs in server01, that connect to server02 and put server02 @@spid in a table. After, when server01 back to server02 (in the same connection), it knows what is your "number".

    So... bacause connection pooling (I think), when server01 connect to server02, server02 give to that connection a number, when server01 back to server02, the @@spid is changed (I dont disconnect, i just wait some moments to query the @@spid). I suspect the sp_reset_connection put the connection in a "dormant" status, and when server01 back to server02, server02 choice another spid to server01.

    |server01 |server02 @@spid|
    |connect |10 |
    |select |11 |

    server02 sp_who who
    10 0 dormant AWAITING COMMAND
    11 0 dormant AWAITING COMMAND

    Any idea?

    I really need the same @@spid on server02 until the server01 connection is active.

    Can I disable connection pooling on SQL?


    Esio Nunes

Share This Page