timeout question

Hello,

I have a connection string that looks like:

"SERVER=MyServer;DATABASE=MyDatabase;UID=Myuser;Connect Timeout=900;"

However, when I try to run the following, it times out after MUCH less
than 15 minutes:

Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal
UpdateConn As SqlConnection)

Dim trnDeleteQuery As SqlTransaction =
UpdateConn.BeginTransaction(IsolationLevel.Serializable, "DeleteQuery")

Dim cmdDeleteQuery As New SqlCommand(UpdateSQL, UpdateConn,
trnDeleteQuery)

Try

Dim iUpdated As Integer

iUpdated = cmdDeleteQuery.ExecuteNonQuery

'trnDeleteQuery.Rollback()

trnDeleteQuery.Commit()

Catch ex As Exception
Throw ex
Finally
If Not cmdDeleteQuery Is Nothing Then
cmdDeleteQuery.Dispose()
If Not trnDeleteQuery Is Nothing Then
trnDeleteQuery.Dispose()
End Try

End Sub

My delete query looks like:

DELETE FROM Table1 WHERE MyID IN (1,2,3,4,5);
DELETE FROM Table2 WHERE MyID IN (1,2,3,4,5)

However, there are about 32000 values in my where.

Running a SELECT query in SQL Server Query Analyzer with the same WHERE
clause took about 8 minutes.

Thanks,
Eric

Jim
Thu Jun 08 15:32:20 CDT 2006

Connection Timeout is not the same as CommandTimeout

Set the cmdDeleteQuery.CommandTimeOut property to an appropriate value.

<eric.goforth@gmail.com> wrote in message
news:1149797581.584327.235750@j55g2000cwa.googlegroups.com...
> Hello,
>
> I have a connection string that looks like:
>
> "SERVER=MyServer;DATABASE=MyDatabase;UID=Myuser;Connect Timeout=900;"
>
> However, when I try to run the following, it times out after MUCH less
> than 15 minutes:
>
> Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal
> UpdateConn As SqlConnection)
>
> Dim trnDeleteQuery As SqlTransaction =
> UpdateConn.BeginTransaction(IsolationLevel.Serializable, "DeleteQuery")
>
> Dim cmdDeleteQuery As New SqlCommand(UpdateSQL, UpdateConn,
> trnDeleteQuery)
>
> Try
>
> Dim iUpdated As Integer
>
> iUpdated = cmdDeleteQuery.ExecuteNonQuery
>
> 'trnDeleteQuery.Rollback()
>
> trnDeleteQuery.Commit()
>
> Catch ex As Exception
> Throw ex
> Finally
> If Not cmdDeleteQuery Is Nothing Then
> cmdDeleteQuery.Dispose()
> If Not trnDeleteQuery Is Nothing Then
> trnDeleteQuery.Dispose()
> End Try
>
> End Sub
>
> My delete query looks like:
>
> DELETE FROM Table1 WHERE MyID IN (1,2,3,4,5);
> DELETE FROM Table2 WHERE MyID IN (1,2,3,4,5)
>
> However, there are about 32000 values in my where.
>
> Running a SELECT query in SQL Server Query Analyzer with the same WHERE
> clause took about 8 minutes.
>
> Thanks,
> Eric
>