473,289 Members | 2,155 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

"Timeout expired" for simple ADO.NET SQL Server query

Hi,

I've made a HttpModule which deals with user authentication. On the first
request in a users session, it fetches data from a SQL Server using the
following code:
using (SqlConnection connection = new
SqlConnection(ConfigurationSettings.AppSettings["Database.Connection"]))
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand("GetAdvisorEnterpriseLogin",
connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@InputUsername",
HttpContext.Current.User.Identity.Name);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
username = (string) reader["Username"];
password = (string) reader["Password"];
reader.Close();
}

The "GetAdvisorEnterpriseLogin" procedure is a really simple one, and hardly
takes 0.1 second to run.
However, the first 1-3 times I access the page after a recompile (and once
in a while otherwise), I get the following exception:

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. Description: An unhandled
exception occurred during the execution of the current web request. Please
review the stack trace for more information about the error and where it
originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.

I also get the exception after not using the system for a while, but I
haven't experienced when the system is "in use", i.e. when the previous
request was no more than a couple of minutes ago.

Does anyone know what could cause this error, and how I can prevent it from
happening?

Regards,
Nils Magnus Englund
Nov 19 '05 #1
3 13928
This can be alleviated by increasing the CommandTimeout. Of course, this
isn't necessarily the best thing to do, but it can be done piecemeal (on a
per-Command basis in your code), in the web.config file, or at a higher
level, although I wouldn't recommend that.

One way to diagnose the issue is to log the time that the Command takes to
execute. This can be done by getting DateTime.Now prior to opening the
Connection, and using DateTime.Subtract() after executing the Command, to
get the TimeSpan elapsed during execution. Example (Hope you don't mind if I
didn't cut out all the stuff you're not interested in):

Overloads Shared Function ExecuteSP(ByVal strProcedureName As String, _
ByVal UseTransaction As Boolean, ByVal CommandTimeout As Integer, _
ByRef SecondsToRun As Integer, Optional ByVal aryParameters As sParam()
= Nothing, _
Optional ByVal p_strConnectionString As String = "") As Boolean

Dim objConn As SqlConnection
Dim objCommand As SqlCommand
Dim objTrans As SqlTransaction = Nothing
Dim strCString As String = p_strConnectionString
Dim intCt As Integer
Dim d As System.DateTime = System.DateTime.Now()
Dim ts As TimeSpan

Try
If strCString = "" Then strCString = ConnectionString
objConn = New SqlConnection(strCString)
objConn.Open()
objCommand = objConn.CreateCommand()
If UseTransaction Then
objTrans = objConn.BeginTransaction()
End If
objCommand.Connection = objConn
If UseTransaction Then
objCommand.Transaction = objTrans
End If
If CommandTimeout > 0 Then objCommand.CommandTimeout =
CommandTimeout

Try
objCommand.CommandText = strProcedureName
objCommand.CommandType = CommandType.StoredProcedure
If Not IsNothing(aryParameters) Then
For intCt = 0 To aryParameters.Length - 1
objCommand.Parameters.Add(aryParameters(intCt).Nam e,
aryParameters(intCt).Type).Value = aryParameters(intCt).Value
objCommand.Parameters(objCommand.Parameters.Count -
1).Direction = aryParameters(intCt).Direction
objCommand.Parameters(objCommand.Parameters.Count -
1).Size = aryParameters(intCt).Size
Next
End If
objCommand.ExecuteNonQuery()
If UseTransaction Then
objTrans.Commit()
End If
Catch ex0 As SqlException
Utilities.LogError(GetSqlException(ex0))
Utilities.HandleError(ex0, False, "Exception " & _
" occurred Rolling Back Stored Procedure " & _
strProcedureName)
If UseTransaction AndAlso ex0.Message.IndexOf("Timeout
expired") < 0 Then
Try
objTrans.Rollback()
Catch ex1 As SqlException
Utilities.LogError(GetSqlException(ex0))
Utilities.HandleError(ex0, False, "Exception " & _
" occurred Rolling Back Stored Procedure " & _
strProcedureName)
Return False
Catch invex As InvalidOperationException
Utilities.HandleError(invex, False, _
"InvalidOperation Exception occurred during
rollback of transaction associated with Stored Procedure '" & _
strProcedureName & "'")
Return False
Catch ex As Exception
Utilities.HandleError(ex, True, "Exception of Type "
& _
ex.GetType().ToString() & " occurred Executing
Stored Procedure " & _
strProcedureName)
Return False
End Try
End If
Return False
End Try
Return True
Catch ex2 As Exception
Utilities.HandleError(ex2, True, "Exception of Type " & _
ex2.GetType().ToString() & " occurred Executing Stored
Procedure " & _
strProcedureName)
Return False
Finally
CloseConn(objConn, objCommand)
If Not IsNothing(objTrans) Then objTrans.Dispose()
ts = System.DateTime.Now.Subtract(d)
SecondsToRun = CInt(ts.TotalSeconds)
End Try
End Function

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

"Nils Magnus Englund" <ni*****************@orkfin.no> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
Hi,

I've made a HttpModule which deals with user authentication. On the first
request in a users session, it fetches data from a SQL Server using the
following code:
using (SqlConnection connection = new
SqlConnection(ConfigurationSettings.AppSettings["Database.Connection"]))
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand("GetAdvisorEnterpriseLogin",
connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@InputUsername",
HttpContext.Current.User.Identity.Name);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
username = (string) reader["Username"];
password = (string) reader["Password"];
reader.Close();
}

The "GetAdvisorEnterpriseLogin" procedure is a really simple one, and
hardly
takes 0.1 second to run.
However, the first 1-3 times I access the page after a recompile (and once
in a while otherwise), I get the following exception:

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. Description: An unhandled
exception occurred during the execution of the current web request. Please
review the stack trace for more information about the error and where it
originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.
The
timeout period elapsed prior to completion of the operation or the server
is
not responding.

I also get the exception after not using the system for a while, but I
haven't experienced when the system is "in use", i.e. when the previous
request was no more than a couple of minutes ago.

Does anyone know what could cause this error, and how I can prevent it
from
happening?

Regards,
Nils Magnus Englund

Nov 19 '05 #2
"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote:
This can be alleviated by increasing the CommandTimeout. Of course, this
isn't necessarily the best thing to do, but it can be done piecemeal (on a
per-Command basis in your code), in the web.config file, or at a higher
level, although I wouldn't recommend that.

Hi Kevin,

Thanks for your reply, but unfortunately, increasing CommandTimeout or
setting it to 0 didn't help at all. When the code doesn't throw a Timeout
exception, the procedure takes under 0.1 seconds to run - which should be
well within the default CommandTimeout (which I believe is 30 seconds)?
Regards,
Nils Magnus Englund


Nov 19 '05 #3
Hi Nils,

It sounds like the problem may be on your SQL Server. You'll have to do some
diagnosis there. Sometimes, for example, a SQL Server can get very busy
doing database backups, Transaction Log backups, or even handling other jobs
or client tasks. Another possible issue might be network latency. If the
network connection goes down, or gets bogged down for some reason, it can
cause the Command to time out eventually. The CommandTimeout is a property
of the SqlCommand class, and indicates how long it will wait for a response.
It does nothing on the SQL Server itself.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

"Nils Magnus Englund" <ni*****************@orkfin.no> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote:
This can be alleviated by increasing the CommandTimeout. Of course, this
isn't necessarily the best thing to do, but it can be done piecemeal (on
a per-Command basis in your code), in the web.config file, or at a higher
level, although I wouldn't recommend that.

Hi Kevin,

Thanks for your reply, but unfortunately, increasing CommandTimeout or
setting it to 0 didn't help at all. When the code doesn't throw a Timeout
exception, the procedure takes under 0.1 seconds to run - which should be
well within the default CommandTimeout (which I believe is 30 seconds)?
Regards,
Nils Magnus Englund

Nov 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ed Burns | last post by:
Hi. I am trying to disable a user from going back to a previous page and displaying information previously shown. I want to give them the typical "Page has Expired" warning message. I am using Win...
0
by: Sham Yemul | last post by:
Hello, We developed an application that has many data entry forms and data controls in Vb.net and Sql Server2000 as backend. For this application we created connection object on load event of...
3
by: Carpe Diem | last post by:
Hello I have an aspx page that loses Session("user") value after a few minutes even after I set <sessionState mode="InProc" cookieless="false" timeout="300"> in web.config and wrote function...
2
by: Chris Langston | last post by:
I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server that is experiencing strange shutdown problems. We are using ASP.NET v1.1 and our application is written in VB.NET ...
2
by: Nils Magnus Englund | last post by:
Hi, I've made a HttpModule which deals with user authentication. On the first request in a users session, it fetches data from a SQL Server using the following code: using (SqlConnection...
3
by: Agnes | last post by:
My client said when he got "Timeout Expired", once he press the button. whole applciation is quit. I want to "produce the same error " in my development environment but don't know how to do ?...
20
by: Wes Groleau | last post by:
I was doing update statements in SQL Server 2000. I have a table with over 16 million rows. It came from several hundred delimited text files, and two of the columns are file ID (int) and Line...
1
by: ahmnasa | last post by:
I have a website that has been running for over 1 year now that has never had these issues. Its running MS SQL Server 2005 Express Edition and IIS6 ASP 3 coded. All running from one server. I...
1
by: anudu | last post by:
hi all, I am developing a system using asp.net(c#). I am using "left" "top" & "Middle" design in my system. I want to set a time out mechanism. when the user has expired the configured time...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.