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.Subtra ct() 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 strProcedureNam e As String, _
ByVal UseTransaction As Boolean, ByVal CommandTimeout As Integer, _
ByRef SecondsToRun As Integer, Optional ByVal aryParameters As sParam()
= Nothing, _
Optional ByVal p_strConnection String As String = "") As Boolean
Dim objConn As SqlConnection
Dim objCommand As SqlCommand
Dim objTrans As SqlTransaction = Nothing
Dim strCString As String = p_strConnection String
Dim intCt As Integer
Dim d As System.DateTime = System.DateTime .Now()
Dim ts As TimeSpan
Try
If strCString = "" Then strCString = ConnectionStrin g
objConn = New SqlConnection(s trCString)
objConn.Open()
objCommand = objConn.CreateC ommand()
If UseTransaction Then
objTrans = objConn.BeginTr ansaction()
End If
objCommand.Conn ection = objConn
If UseTransaction Then
objCommand.Tran saction = objTrans
End If
If CommandTimeout > 0 Then objCommand.Comm andTimeout =
CommandTimeout
Try
objCommand.Comm andText = strProcedureNam e
objCommand.Comm andType = CommandType.Sto redProcedure
If Not IsNothing(aryPa rameters) Then
For intCt = 0 To aryParameters.L ength - 1
objCommand.Para meters.Add(aryP arameters(intCt ).Name,
aryParameters(i ntCt).Type).Val ue = aryParameters(i ntCt).Value
objCommand.Para meters(objComma nd.Parameters.C ount -
1).Direction = aryParameters(i ntCt).Direction
objCommand.Para meters(objComma nd.Parameters.C ount -
1).Size = aryParameters(i ntCt).Size
Next
End If
objCommand.Exec uteNonQuery()
If UseTransaction Then
objTrans.Commit ()
End If
Catch ex0 As SqlException
Utilities.LogEr ror(GetSqlExcep tion(ex0))
Utilities.Handl eError(ex0, False, "Exception " & _
" occurred Rolling Back Stored Procedure " & _
strProcedureNam e)
If UseTransaction AndAlso ex0.Message.Ind exOf("Timeout
expired") < 0 Then
Try
objTrans.Rollba ck()
Catch ex1 As SqlException
Utilities.LogEr ror(GetSqlExcep tion(ex0))
Utilities.Handl eError(ex0, False, "Exception " & _
" occurred Rolling Back Stored Procedure " & _
strProcedureNam e)
Return False
Catch invex As InvalidOperatio nException
Utilities.Handl eError(invex, False, _
"InvalidOperati on Exception occurred during
rollback of transaction associated with Stored Procedure '" & _
strProcedureNam e & "'")
Return False
Catch ex As Exception
Utilities.Handl eError(ex, True, "Exception of Type "
& _
ex.GetType().To String() & " occurred Executing
Stored Procedure " & _
strProcedureNam e)
Return False
End Try
End If
Return False
End Try
Return True
Catch ex2 As Exception
Utilities.Handl eError(ex2, True, "Exception of Type " & _
ex2.GetType().T oString() & " occurred Executing Stored
Procedure " & _
strProcedureNam e)
Return False
Finally
CloseConn(objCo nn, objCommand)
If Not IsNothing(objTr ans) Then objTrans.Dispos e()
ts = System.DateTime .Now.Subtract(d )
SecondsToRun = CInt(ts.TotalSe conds)
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******** ********@TK2MSF TNGP14.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(C onfigurationSet tings.AppSettin gs["Database.Conne ction"]))
{
connection.Open ();
SqlCommand sqlCommand = new SqlCommand("Get AdvisorEnterpri seLogin",
connection);
sqlCommand.Comm andType = CommandType.Sto redProcedure;
sqlCommand.Para meters.Add("@In putUsername",
HttpContext.Cur rent.User.Ident ity.Name);
SqlDataReader reader = sqlCommand.Exec uteReader();
reader.Read();
username = (string) reader["Username"];
password = (string) reader["Password"];
reader.Close();
}
The "GetAdvisorEnte rpriseLogin" 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.Sql Client.SqlExcep tion: 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
