Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Sql Server: "All I need is a little more time"

Our client is a windows form app.  Our DAL is hosted by IIS on the same box as the sql server database.  All select sprocs work perfect, no problems. The app connects to the DAL, which connects to Sql Server, Resultsets are passed back to the client through the remote type, no problems.

On the first call to do an insert or update sproc though we see this exception:

Index #0

Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

LineNumber: 0

Source: .Net SqlClient Data Provider

Procedure: ConnectionRead (recv()).

Severity Level: -2

So, we try to do a save from the client again, same thing. We try a 3rd time and all works well over and over and over. No more timeouts.  This happens every time, no matter the time of day or workload on the server, the first 2 times, then it always works fine.

This only happens on a sql server 2000 box.  Our development box is sql server 7 and we never get a timeout exception.

Bob Beauchemin suggest I increaded the command timeout.  So, rather than use the default 30, I explicitly set it to 60 right before the calls to Update on SqlDataAdapter. Know what?  No more timeout exceptions and it only takes 1-2 seconds.  This one baffles me as all I really did was explicitly set the CommandTimeout = 60 and no more exceptions on the fist 2 updates.

I would love to hear an explanation for this.  Not sure if the sql server 7 vs. 2000 is why though it is a difference in the 2 environements.

 

6 Comments

  • Check the SQL Service Pack level on the server, and what networking protocol are using for the connection?

  • Both sql server installs are using TCP-IP.



    On the box where we see the behavior:



    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05

    Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows

    NT 5.0 (Build 2195: Service Pack 4)



    On the box where we do not see this behavior:



    Microsoft SQL Server 7.00 - 7.00.1077 (Intel X86) Sep 6 2002 15:10:15 Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)



  • Well, unfortunately this exception has raised it's ugly head again and the setting of the command connectiontimeout to 60 is in place.

  • Did you deploy with an install-script?

  • Hi,

    I am also facing the same problem, any solution?

    Thanks in advance,
    vijay

  • Hi,

    The connection timeout issue can be solved using the
    dbcommandwrapper object of commandtimeout property.
    But,
    instead of using dbcommandwrapper, does anyone know how to set that parameter in connection string?

    Thanks,
    Vijay

Comments have been disabled for this content.