SqlDataAdapter use results in SQL Server processes 'AWAITING COMMAND'?

[MikeL04]

I originally posted this in the ASP.Net forum but have not recieved a reponse and the problem is getting worse with SQL Server 2000 maintaining many proceses for what seems to be no reason. The SQL stored proc is listed at the bottom of this post.

I have a web app in ASP.Net that uses a Data Access Logic Component that has the following method to return a DataRow to the calling business logic. This may be called 10+ times on rendering the aspx page to the client depending on the number of pannels with dynamic help on a page. I have ran into the problem of getting SQL connection errors recently and discovered that each time this method is called it creates a SQL process in the sleeping 'AWAITING COMMAND' state referencing the associated stored proceedure from this method. After using the web app for 30 minutes the number of processes like this can top 200 resulting in my connection issues.

My understanding was that the SqlDataAdapter managed these types of connections and disposed of them once the command was complete which is not happening for me here. Especially since the purpose of the SqlDataAdapter and DataSet is for diconnected data access. I have tried manually disposing of everything from th econnection, command, and data adapter objects when finished with them but the result is the same.

Is this accumulation of SQL Server processes normal when using the SqlDataAdapter or am I doing something wrong.. or forgetting to do something?


Any help or insight appreciated.

TIA,

Mike

Public Function GetHelpItem(ByVal WebUserHelpLabel As String, ByVal LanguageID As Integer) As DataRow
    Dim cmd As New SqlCommand("WebUserHelp_GetItemByLabelID", conn)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SqlParameter("@WebUserHelpLabel", SqlDbType.VarChar))
    cmd.Parameters("@WebUserHelpLabel").Value = WebUserHelpLabel
    cmd.Parameters.Add(New SqlParameter("@LanguageID", SqlDbType.Int))
    cmd.Parameters("@LanguageID").Value = LanguageID

    Dim da As New SqlDataAdapter(cmd)
    Dim ds As New DataSet
    Dim dr As DataRow

    da.Fill(ds, "HelpItem")
    
    If ds.Tables(0).Rows.Count = 0 Then
      Dim rowVals(1) As Object
      rowVals(0) = ""
      rowVals(1) = ""

      ds.Tables(0).Rows.Add(rowVals)
    End If

    dr = ds.Tables(0).Rows(0)

    Return dr
  End Function

The Stored proceedure "WebUserHelp_GetItemByLabelID" is as follows:

CREATE    PROCEDURE dbo.WebUserHelp_GetItemByLabelID
    @WebUserHelpLabel varchar(50)
    ,@LanguageID      int
AS

SET NOCOUNT ON

SELECT
    m.HelpTextBackEnd
    ,m.HelpTextFrontEnd
FROM
    main_WebUserHelp            h
    JOIN main_WebUserHelp_Multilingual m ON h.WebUserHelpID = m.WebUserHelpID
WHERE
    h.WebUserHelpLabel = @WebUserHelpLabel
    AND m.LanguageID = @LanguageID

 

[DLSQL]

We are experiencing a similar issue with our ASP.NET application. I will check with our principal programmer on that to see if he has any resolution

DL
MCDBA, MCSD, MCT, etc.

 

[MikeL04]

Thanks for the response but I found it. We have a class (say MyPage) that inherits from System.Web.UI.Page that we use for all our aspx pages that provides the user and role control and application config etc. In this class a cusom translator class is instantiated opening a db connection and performs any translations on a page. The connection is closed when MyPage is disposed. Because I implemented the WebUserHelp module as rendered server control, it did not inherit from our MyPage but instead instantiates its own translator object thus opening the connection. Because the .dispose() is not called as is it in MyPage the connection was getting left open. Withing my server control I now close the connection when finished with the localized translator object and the problem is solved.

I was missled originally as to where the connection was being leaked, it is not a result of the use a DataAdapter like I originally thought.

Thanks for the time and space.

Mike