SQL exception timeout

[si_owen]

Hi Guys,

I am running visual studio 2005 with asp.net v2.

I am calling records back from a database over a time period.
The application works great over a time period of less than 2 months,
however for over 2 months I get the following error:

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

I have changed the timeout property in sql enterprise manager to 60
(seconds)

and have the following line now in my code:

exceptionsSqlCommand.CommandTimeout = 60

Can anyone shed any light on how to fix this problem?? I have read some
references to changinh the machine.config file??

Any help wuld be much apprecieated,

Simon

 

[Robinson]

Have you set the timeout in the connection string?

 

[Marina Levit [MVP]]

Your timeout is 60 seconds - is your query taking beyond that?

Is it also possible that there is an issue with the server, such as database
deadlocking or the server is overwhelmed?

Normally changing the CommandTimeout is all that is necessary.

 

[si_owen]

I have set it in the command line and in enterprise manager only.

How do u set it in the connection string?

I am timing they load at currently 30 seconds before timeout, so its
not even reaching the new 60 seconds constraint I have set.

any ideas??

Simon

 

[Robinson]

Well when it comes to timing, there are a lot of possible variables in there
you know - such as how the query is constructed. It isn't neccessarily
linear in time. Anyway, you can put the timeout in the connection string
with:

; CommandTimeout=60;

Post some code and it will be easier to see. Are you executing a stored
procedure?

 

[si_owen]

no stored procedure heres the code that i have added recently that has
slowed down the data recovery:

For Each tempItem In RadGrid1.MasterTableView.Items

If tempItem.Cells(6).Text <> "0" Then
Dim tokenString As String = tempItem.Cells(7).Text
Dim dateString As String = tempItem.Cells(2).Text
Dim transactionTimeString As String =
tempItem.Cells(3).Text

Dim exceptionsSqlString As String = "SELECT
tblExep.TransactionTime, tblExep.TokenNumber, tblExep.Date,
tblExepType.fNumber, tblExep.ExceptionType, tblExepType.fName FROM
PFWTRAN.dbo.Exceptions AS tblExep INNER JOIN dbo.ExceptionTypes AS
tblExepType ON tblExepType.fNumber = tblExep.ExceptionType WHERE
(tblExep.TokenNumber = " & tokenString & ") AND (tblExep.Date = " &
dateString & ") AND (tblExep.TransactionTime = " &
transactionTimeString & ")"
Dim exceptionsSqlConnection As New
SqlConnection(ConfigurationManager.AppSettings("Plantime.ConnectionString"))
Dim exceptionsSqlCommand As New
SqlCommand(exceptionsSqlString, exceptionsSqlConnection)
exceptionsSqlCommand.CommandTimeout = 60
Dim exceptionsSqlDataReader As SqlDataReader

tempItem.Cells(6).Text = ""
Dim count As Integer = 0

exceptionsSqlConnection.Open()
exceptionsSqlDataReader =
exceptionsSqlCommand.ExecuteReader

While exceptionsSqlDataReader.Read
If count > 0 Then
tempItem.Cells(6).Text &= ", "
End If

Dim infoString As String = ""

Select Case
RTrim(LTrim(UCase(exceptionsSqlDataReader(5))))
Case Is = "NO BREAK"
infoString = "You did not swipe out for
lunch"
Case Is = "SHORT BREAK"
infoString = "Your lunch break was less
than 30 minutes"
Case Is = "CORE AM"
infoString = "You swiped between 9:30 and
12:00"
Case Is = "CORE PM"
infoString = "You swiped between 14:00 and
16:30"
Case Is = "NOT OUT"
infoString = "You did not swipe out at the
end of the working day"
End Select

tempItem.Cells(6).Text &= "<a title='" & infoString
& "'>" & exceptionsSqlDataReader(5) & "</a>"
count += 1
End While

exceptionsSqlConnection.Close()
Else
tempItem.Cells(6).Text = "---"
End If

..............................................next

could you please point out where exactly to place the timeout line in
the connection string as when I try I get errors in the code.

Cheers,

Simon

 

[Marina Levit [MVP]]

You can't, this is not a connection level property. It is a command level
property.

I have never heard of the CommandTimeout property not being honored. Perhaps
there is something else going on such that the code isn't running in the way
you think it is.