ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection problems

Discussion in 'ASP .Net' started by =?Utf-8?B?Z2FsaWNv?=, Jul 3, 2006.

  1. Hi All,

    We are having a very strange problem with the above. We have designed an
    application in ASP.NET 2.0 that uses the enterprise library data application
    blocks amongst others. We seem to be maxing out the number of connections
    allowed to the sql server 7 server after running a few large queries through
    the application? There are less than 1000 rows of data in the database and we
    are at a loss as to how to fix this, does anyone have any ideas?

    Thanks in advance

    Andy
    =?Utf-8?B?Z2FsaWNv?=, Jul 3, 2006
    #1
    1. Advertising

  2. Hi Andy,

    Welcome to the ASPNET newsgroup.

    From your description, I understand you're developing an ASP.NET 2.0 web
    application which use the enterprise library 2.0 (the data application
    block) to connect a sqlserver 7.0 database. However, you found that the sql
    server is suffering a connection booming issue when the ASP.NET application
    running, correct? If anything I missed , please feel free to correct me.

    As for the ADO.NET data accessing, the underlying database connections are
    generally managed by the specific data access provider. For your scenario,
    I think you're using the sqlserver provider(.net managed provider)
    ,correct? Also, since you're using data application block in the enterprise
    library, all the ADO.NET connection management pattern are done by the
    application block, it should not suffer any obvious connection leak issue.
    Anyway, for basic troubleshooting, we can first isolate the problem
    scenario and here are something we can check first:

    1. what's the connection string (use sql or windows authentication), for
    sqlserver provider, when the connection pool is based on connectionstring,
    when using sql quthentication(embeded username/password in
    connectionstring) the connection pool's has best effiency. When using
    windows authentication, it will use subpools (according to different login
    user). You can check your one and verify whether the issue is specific to a
    certain authentication type.

    2. whether the issue is data applcationblock (entlib). for testing, you can
    try locate the code period which will result the connection booming and
    them use pure ADO.NET code(without using enterprise library) to see whether
    the problem is specific to entlib.

    3. use sql profiler to trace teh connection states at sqlserver side.
    For monitoring the connection behavior at sqlserver side(if you have the
    sufficient privillege), sql profiler is a good tool to trace the connection
    or execution operations.

    Hope this helps some. If there is any other findings, please feel free to
    post here.

    Regards,

    Steven Cheng
    Microsoft MSDN Online Support Lead


    ==================================================

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    ==================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    Steven Cheng[MSFT], Jul 4, 2006
    #2
    1. Advertising

  3. RE: ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection prob

    Hi Steven,

    Thanks for your help. Here is a sample of the code we use to call the
    enterprise library, followed by our connection string. I'll try your
    suggestions, please advise of anything else you think of.

    Code
    ------
    //Use the Enterprise Library DatabaseFactory to fetch the BCP
    Database db = DatabaseFactory.CreateDatabase();

    //Scope the DataReader so it will be disposed after the read
    is complete
    using (IDataReader reader = db.ExecuteReader("FetchSetBCP",
    this.BCPID, this.CCMID,

    this.ChangeTypeID,this.DateCompleted,this.Title,this.RequiredDate,this.AreaID,

    this.ChangeDescription,this.BenefitDescription,this.TotalAnualBenefit,
    this.CategoryID, this.AlreadyDiscussed,
    this.HeatRef, this.DateSubmitted, this.BCPReference,
    this.BenefitOwner,this.Confidential,this.RejectNotes,
    this.NonFinancialBenefits,this.CompletedBy))
    {
    List<BCP> list = new List<BCP>();

    while (reader.Read())
    {
    BCP bcp = new BCP();

    //fill the BCP properties
    fillProperties(reader, bcp);

    list.Add(bcp);
    }
    return list;
    }

    end code
    ----------

    connection string
    -------------------
    <add name="CCM" connectionString="Data Source=SQLCAR002;Initial
    Catalog=CCM;User Id=CCMDBUser;password=password; pooling=false;"
    providerName="System.Data.SqlClient"/>

    Thanks,

    Andy

    "Steven Cheng[MSFT]" wrote:

    > Hi Andy,
    >
    > Welcome to the ASPNET newsgroup.
    >
    > From your description, I understand you're developing an ASP.NET 2.0 web
    > application which use the enterprise library 2.0 (the data application
    > block) to connect a sqlserver 7.0 database. However, you found that the sql
    > server is suffering a connection booming issue when the ASP.NET application
    > running, correct? If anything I missed , please feel free to correct me.
    >
    > As for the ADO.NET data accessing, the underlying database connections are
    > generally managed by the specific data access provider. For your scenario,
    > I think you're using the sqlserver provider(.net managed provider)
    > ,correct? Also, since you're using data application block in the enterprise
    > library, all the ADO.NET connection management pattern are done by the
    > application block, it should not suffer any obvious connection leak issue.
    > Anyway, for basic troubleshooting, we can first isolate the problem
    > scenario and here are something we can check first:
    >
    > 1. what's the connection string (use sql or windows authentication), for
    > sqlserver provider, when the connection pool is based on connectionstring,
    > when using sql quthentication(embeded username/password in
    > connectionstring) the connection pool's has best effiency. When using
    > windows authentication, it will use subpools (according to different login
    > user). You can check your one and verify whether the issue is specific to a
    > certain authentication type.
    >
    > 2. whether the issue is data applcationblock (entlib). for testing, you can
    > try locate the code period which will result the connection booming and
    > them use pure ADO.NET code(without using enterprise library) to see whether
    > the problem is specific to entlib.
    >
    > 3. use sql profiler to trace teh connection states at sqlserver side.
    > For monitoring the connection behavior at sqlserver side(if you have the
    > sufficient privillege), sql profiler is a good tool to trace the connection
    > or execution operations.
    >
    > Hope this helps some. If there is any other findings, please feel free to
    > post here.
    >
    > Regards,
    >
    > Steven Cheng
    > Microsoft MSDN Online Support Lead
    >
    >
    > ==================================================
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > ==================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    >
    >
    >
    >
    =?Utf-8?B?Z2FsaWNv?=, Jul 4, 2006
    #3
  4. RE: ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection prob

    Thanks for your response Andy,

    From the code snippet you provided, the code logic is correct and there is
    no particular code problem here. And you've put datareader in using ()
    block which may ensure the datareader be disposed. Also, the Enterprise
    library/Data App block will also ensure that connection be closed after
    reader be closed(in the ExecuteReader method). So I don't think this is a
    entlib specific issue, but to verify this you can still use some standard
    ADO.NET code(without using entlib) to perform the query from the sqlserver
    database to see whether it still suffer the issue.

    Also, make sure to perform some sql profiler trace at the sqlserver
    server-side to see the actual connection open/close behavior when it
    suffers the problem.

    In addition, from the connectionstring you provided, the connection pooling
    ins turned off, have you tried turn it on to see whether the behavior has
    any difference?

    =========
    <add name="CCM" connectionString="Data Source=SQLCAR002;Initial
    Catalog=CCM;User Id=CCMDBUser;password=password; pooling=false;"
    providerName="System.Data.SqlClient"/>
    =========

    Thanks & Regards,

    Steven Cheng
    Microsoft MSDN Online Support Lead


    ==================================================

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    ==================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    Steven Cheng[MSFT], Jul 5, 2006
    #4
  5. RE: ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection prob

    Hi Steven,

    Thanks for the peer check! i appreciate it, and for your suggestion, we 'll
    have a look at that.

    I thought i should bring you up to speed on some of our own testing that we
    have done. We have connected the application to a SQL 2000 version of the
    database and can't seem to get the error, could it be a SQL 7 and .NET 2.0
    issue?

    We have also raised a Premier Support incident (SRQ060704601731) here in the
    UK, i don't know if you can liase with the people helping us or not, we have
    not had anything really useful back yet though.

    Thanks,

    Andy


    "Steven Cheng[MSFT]" wrote:

    > Thanks for your response Andy,
    >
    > From the code snippet you provided, the code logic is correct and there is
    > no particular code problem here. And you've put datareader in using ()
    > block which may ensure the datareader be disposed. Also, the Enterprise
    > library/Data App block will also ensure that connection be closed after
    > reader be closed(in the ExecuteReader method). So I don't think this is a
    > entlib specific issue, but to verify this you can still use some standard
    > ADO.NET code(without using entlib) to perform the query from the sqlserver
    > database to see whether it still suffer the issue.
    >
    > Also, make sure to perform some sql profiler trace at the sqlserver
    > server-side to see the actual connection open/close behavior when it
    > suffers the problem.
    >
    > In addition, from the connectionstring you provided, the connection pooling
    > ins turned off, have you tried turn it on to see whether the behavior has
    > any difference?
    >
    > =========
    > <add name="CCM" connectionString="Data Source=SQLCAR002;Initial
    > Catalog=CCM;User Id=CCMDBUser;password=password; pooling=false;"
    > providerName="System.Data.SqlClient"/>
    > =========
    >
    > Thanks & Regards,
    >
    > Steven Cheng
    > Microsoft MSDN Online Support Lead
    >
    >
    > ==================================================
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > ==================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    >
    >
    >
    =?Utf-8?B?Z2FsaWNv?=, Jul 5, 2006
    #5
  6. RE: ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection prob

    Thanks for your followup Andy,


    We have connected the application to a SQL 2000 version of the
    database and can't seem to get the error, could it be a SQL 7 and .NET 2.0
    issue?
    =======================
    Yes, actually this is the first thing I've tried checking in our internal
    case library and I didn't find any known issue of connecting SQL Server 7.0
    with .net framework.


    We have also raised a Premier Support incident (SRQ060704601731) here in
    the
    UK, i don't know if you can liase with the people helping us or not, we
    have
    not had anything really useful back yet though.
    ===============================
    I think this the most efficient way since the product support engineer will
    help you perform some thorough throubleshooting. And I think they'll also
    ask you to capture some trace log since this will be important.

    So far I haven't any further information about this issue since this is
    likely a project specific one. Anyway, if you got any new update from the
    product team, please feel free to post here.

    Regards,

    Steven Cheng
    Microsoft MSDN Online Support Lead


    ==================================================

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    ==================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
    Steven Cheng[MSFT], Jul 6, 2006
    #6
  7. RE: ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection prob

    Hi Steven,

    Just thought i'd give you an update. This may be beneficial to other users
    here too. Since we moved the application database from SQL Server 7.0 to SQL
    Server 2000 the problem has not occured. We believe that there are issues
    with ASP.NET 2.0/ADO.NET 2.0/Enterprise Library 2006 and SQL Server 7.0.

    The application also runs much quicker. Just thought i should let you know.

    Andy

    "Steven Cheng[MSFT]" wrote:

    > Thanks for your followup Andy,
    >
    >
    > We have connected the application to a SQL 2000 version of the
    > database and can't seem to get the error, could it be a SQL 7 and .NET 2.0
    > issue?
    > =======================
    > Yes, actually this is the first thing I've tried checking in our internal
    > case library and I didn't find any known issue of connecting SQL Server 7.0
    > with .net framework.
    >
    >
    > We have also raised a Premier Support incident (SRQ060704601731) here in
    > the
    > UK, i don't know if you can liase with the people helping us or not, we
    > have
    > not had anything really useful back yet though.
    > ===============================
    > I think this the most efficient way since the product support engineer will
    > help you perform some thorough throubleshooting. And I think they'll also
    > ask you to capture some trace log since this will be important.
    >
    > So far I haven't any further information about this issue since this is
    > likely a project specific one. Anyway, if you got any new update from the
    > product team, please feel free to post here.
    >
    > Regards,
    >
    > Steven Cheng
    > Microsoft MSDN Online Support Lead
    >
    >
    > ==================================================
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > ==================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    >
    >
    >
    >
    =?Utf-8?B?Z2FsaWNv?=, Aug 1, 2006
    #7
  8. RE: ASP.NET 2.0, Enterprise Library 2006 and SQL 7 connection prob

    Hi Andy,

    Thanks for your followup and the updated information. It is really
    appreciated and will benifit other community member who encountering the
    similar problem.

    Have a good day!

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead
    Steven Cheng[MSFT], Aug 2, 2006
    #8
  9. =?Utf-8?B?Z2FsaWNv?=

    aashishgandhi

    Joined:
    Nov 13, 2007
    Messages:
    1
    We are facing a similar issue. When we were using .NET Framework v 1.1 and connecting to SQL Server 7, we didn't have any issues, but when we moved to v 2.0, we started getting this error -
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
    It's not that we are able to connect at all, but only at certain times we are getting this error probably due to increase in the load, but not sure.
    aashishgandhi, Nov 13, 2007
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Herbst
    Replies:
    1
    Views:
    3,230
    David Herbst
    Jan 29, 2006
  2. John Hatcliff
    Replies:
    0
    Views:
    390
    John Hatcliff
    Aug 23, 2005
  3. Wade
    Replies:
    3
    Views:
    569
    =?Utf-8?B?TXVydGF6YQ==?=
    Jul 25, 2006
  4. rockdale
    Replies:
    7
    Views:
    7,402
    rockdale
    Aug 29, 2006
  5. TattedProgrammer
    Replies:
    0
    Views:
    3,860
    TattedProgrammer
    Aug 31, 2006
Loading...

Share This Page