April 29, 2008 at 1:10 pm
I am attempting to change the command time-out for a call to a SQL stored procedure with VB.NET in and ASP.NET dev model (code is included below).
While I believe that everything is structured properly and there are no syntax errors, the call still times out in 30 seconds no matter what settings I feed it. Has anyone wrestled with this before? This code is calling an sp in a db linked to the production db. What else could be controlling the time out?
Thanks, John
======================================================
Public Function FFSNONFHCP(ByVal vDOSStart As Date, ByVal vDOSEnd As Date, ByVal vPAYStart As Date, ByVal vPAYEnd As Date) As DataSet
Dim oSqlConn As SqlClient.SqlConnection = GetSqlLINK()
Try
Dim oSqlCmdList As SqlClient.SqlCommand = New SqlClient.SqlCommand("aaFFSNONFHCP", oSqlConn)
Dim oSQLConnectionTimeOut As Sql.SqlNotificationRequest = New Sql.SqlNotificationRequest
oSQLConnectionTimeOut.Timeout = 30
Dim oSQLCommandTimeOut As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
oSQLCommandTimeOut.CommandTimeout = 1000
oSqlCmdList.CommandType = CommandType.StoredProcedure
Dim oSqlParm(4) As Data.SqlClient.SqlParameter
oSqlParm(0) = New SqlClient.SqlParameter("@DOSStart", vDOSStart)
oSqlParm(1) = New SqlClient.SqlParameter("@DOSEnd", vDOSEnd)
oSqlParm(2) = New SqlClient.SqlParameter("@PAYStart", vPAYStart)
oSqlParm(3) = New SqlClient.SqlParameter("@PAYEnd", vPAYEnd)
For vLoop = 0 To UBound(oSqlParm) - 1
oSqlCmdList.Parameters.Add(oSqlParm(vLoop))
Next vLoop
oSqlConn.Open()
Dim oSQLDataAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
Dim oDataSet As DataSet = New DataSet
oSQLDataAdapter.SelectCommand = oSqlCmdList
oSQLDataAdapter.Fill(oDataSet, "ProcData")
Return oDataSet
Catch oErr As Exception
vSubName = "SQLProc-FHCP-FFSNONFHCP"
ActivityLogAdd(0, "SQL Error", vSubName, oErr.Message)
SendErrorEmail(vSubName, oErr.Message)
Return Nothing
Finally
If oSqlConn.State = Data.ConnectionState.Open Then
oSqlConn.Close()
End If
End Try
End Function
========================================================
April 29, 2008 at 1:44 pm
You set the command timeout on the oSQLCommandTimeOut object but then never use the object again in the function.
You use oSqlCmdList to fill your data adaptor but never set the timeout (so it uses the default 30 seconds).
Keep in mind that ASP.Net will also have an IIS timeout default of 10 minutes (if I remember correctly) that will destroy your thread if you have a process that runs too long.
Finally, you are probably using a thread from your IIS thread pool, so if you have something that takes longer than 30 seconds you are tying up a thread (from a rather limited pool) for a full 30 seconds. If you tie up all of your threads, IIS will not accept new requests.
April 29, 2008 at 3:46 pm
==Thanks. I've not had an occasion to set the timeout so I used what I found elsewhere which really didn't explain the whole topic. The SP is taking about 46 seconds to execute (optimized).
"You set the command timeout on the oSQLCommandTimeOut object but then never use the object again in the function."
==Where should it be used?
You use oSqlCmdList to fill your data adaptor but never set the timeout (so it uses the default 30 seconds).
==How should it be set?
Keep in mind that ASP.Net will also have an IIS timeout default of 10 minutes (if I remember correctly) that will destroy your thread if you have a process that runs too long.
==It is currently set to 20 minutes (the default).
Finally, you are probably using a thread from your IIS thread pool, so if you have something that takes longer than 30 seconds you are tying up a thread (from a rather limited pool) for a full 30 seconds. If you tie up all of your threads, IIS will not accept new requests.
==The IIS on this box only runs the one application for limited staff so that should not be a problem.
April 30, 2008 at 4:43 am
The point about the oSQLCommandTimeOut is that you declare this object and never use it.
Replace:
oSQLCommandTimeOut.CommandTimeout = 1000
With:
oSqlCmdList.CommandTimeout = 1000
The oSqlCmdList object is the one that you are using to fill your data adaptor.
You really should post this type of question on an ASP.Net or VB.Net forum. This forum is not the appropriate place.
May 1, 2008 at 6:34 am
Michael,
Thanks for the information it worked like a charm.
The SQL ServerCentral site has been very helpful to me for some time.
I wasn't sure if the problem I was having was a VB.NET issue or some excentricity with SQL so I had no idea where to start with the question. I appreciate the fact that you were able to help.
John
August 8, 2008 at 9:53 am
It was very very helpfull for me. Thanks a lot!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply