Post reply

Maximum number of sessions has been reached

  • June 29, 2015 at 8:07 am

    #398666

    Hello,

    We have a big software that run a warehouse distribution center, written in .NET

    Backhand is a SQL Server 2008 R2 STD database.

    Now, it seems there is a problem with the sessions not being properly closed after each call to the DB. Here is the message got form SQL:

    DESCRIPTION:A new connection was rejected because the maximum number of connections on session ID 57 has been reached. Close an existing connection on this session and retry.

    In the .NET code, connection is made with the following code:

    If oConn Is Nothing Then oConn = New SqlConnection

    If oConn.State = ConnectionState.Open Then oConn.Close()

    With oConn

    .ConnectionString = "Server=" & Server & ";Database=" & DB & ";User ID=" & User & ";Password=" & Pass & ";Connection Timeout=" & 5 & ";MultipleActiveResultSets=" & True

    .Open()

    End With

    This code is called once, at the opening of the software

    StoredProc are call with the code:

    Try

    oCmd = New SqlCommand

    With oCmd

    .Connection = oConn

    .CommandType = CommandType.StoredProcedure

    .CommandText = StoredProcName

    .Parameters.Add(New SqlParameter("@OID", Barcode))

    End With

    Get_Status = oCmd.ExecuteScalar

    Catch ex As Exception

    Get_Status = Nothing

    Finally

    If oCmd IsNot Nothing Then oCmd.Dispose()

    oCmd = Nothing

    End Try

    So every Command is closed after execution, yet, they stay active in the SQL Server. Is there something I'm missing here??

    thanks for your help!

  • June 29, 2015 at 1:07 pm

    #1808905

    Do you have MultipleActiveResultSets=true?

    It means you can execute multiple commands under the same connection.

    I can't recall exact syntax but you have to use reader.close() or something similar.

    Alex S

  • June 29, 2015 at 2:14 pm

    #1808925

    thanks for the answer. I don't use a DataReader, only retrieve an integer value, so I don't need to close anything

  • June 30, 2015 at 12:09 pm

    #1809257

    Dominic Gagné (6/29/2015)

    thanks for the answer. I don't use a DataReader, only retrieve an integer value, so I don't need to close anything

    I think both datareader and executescalar use a database connection, and from what I've read on the web (sample code), the executescalar uses a connection that can subsequently be closed after use. Maybe you could experiment with closing your connection (note, this is not closing a datareader or execute scalar, but the _connection_).

    For instance, I see the oConn (in your case) get closed after the oCmd (in your case) gets disposed.

    I've learned a few things with shaking up some dot net code, but clearly you still want to test plenty! Obviously just a suggestion, and I could be completely full of it!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply