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:

exceptionsSqlCo mmand.CommandTi meout = 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]

Re: SQL exception timeout

Have you set the timeout in the connection string?

[Marina Levit [MVP]]

Re: SQL exception timeout

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" <s.owen@sstaffs .gov.ukwrote in message
news:1166106951 .805227.215540@ 79g2000cws.goog legroups.com...

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:
>
exceptionsSqlCo mmand.CommandTi meout = 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
>

[si_owen]

Re: SQL exception timeout

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 wrote:

Have you set the timeout in the connection string?

[Robinson]

Re: SQL exception timeout

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" <s.owen@sstaffs .gov.ukwrote in message
news:1166112071 .209462.193620@ j72g2000cwa.goo glegroups.com.. .

>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 wrote:
>

>Have you set the timeout in the connection string?

>

[si_owen]

Re: SQL exception timeout

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

For Each tempItem In RadGrid1.Master TableView.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 transactionTime String As String =
tempItem.Cells( 3).Text

Dim exceptionsSqlSt ring As String = "SELECT
tblExep.Transac tionTime, tblExep.TokenNu mber, tblExep.Date,
tblExepType.fNu mber, tblExep.Excepti onType, tblExepType.fNa me FROM
PFWTRAN.dbo.Exc eptions AS tblExep INNER JOIN dbo.ExceptionTy pes AS
tblExepType ON tblExepType.fNu mber = tblExep.Excepti onType WHERE
(tblExep.TokenN umber = " & tokenString & ") AND (tblExep.Date = " &
dateString & ") AND (tblExep.Transa ctionTime = " &
transactionTime String & ")"
Dim exceptionsSqlCo nnection As New
SqlConnection(C onfigurationMan ager.AppSetting s("Plantime.Con nectionString") )
Dim exceptionsSqlCo mmand As New
SqlCommand(exce ptionsSqlString , exceptionsSqlCo nnection)
exceptionsSqlCo mmand.CommandTi meout = 60
Dim exceptionsSqlDa taReader As SqlDataReader

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

exceptionsSqlCo nnection.Open()
exceptionsSqlDa taReader =
exceptionsSqlCo mmand.ExecuteRe ader

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

Dim infoString As String = ""

Select Case
RTrim(LTrim(UCa se(exceptionsSq lDataReader(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
& "'>" & exceptionsSqlDa taReader(5) & "</a>"
count += 1
End While

exceptionsSqlCo nnection.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


Robinson wrote:

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" <s.owen@sstaffs .gov.ukwrote in message
news:1166112071 .209462.193620@ j72g2000cwa.goo glegroups.com.. .

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 wrote:

Have you set the timeout in the connection string?

[Marina Levit [MVP]]

Re: SQL exception timeout

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.

"si_owen" <s.owen@sstaffs .gov.ukwrote in message
news:1166112071 .209462.193620@ j72g2000cwa.goo glegroups.com.. .

>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 wrote:
>

>Have you set the timeout in the connection string?

>