SQL Timeout Error -- sql server data mining

11Oppidan
2/2/2005 12:12:21 PM

Hi,

I have a curious situation which I have failed to resolve.

I have a program written in VB .NET which uses ADO.NET to populate a dataset
from MS SQL using a Sqlconnection and a Sqldataadpter. For 99% of the time
it works fine. For 1% of the time an exception is thrown on fill due to a
timeout error on a query that has worked fine twice previously (e.g on the
third pass of the same query it throws an exception). I cannot establish a
pattern as to when it throws an exception - it seems to vary.

Error msg is a follows: SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.

Any advice or suggestions would be greatly appreciated.

The code I am using is as follows:
Public Shared Function ReturnsCmdDataset(ByRef dst As DataSet, ByRef
strdtbName As String, ByRef strcnnSQLS As String, ByRef strSQL As String) As
DataTable

Dim cnn As SqlConnection = New SqlConnection(strcnnSQLS)

Dim adpSQLS As SqlDataAdapter = New SqlDataAdapter

Dim cmd As SqlCommand = New SqlCommand(strSQL, cnn)

Try

cmd.CommandTimeout = 0

adpSQLS.SelectCommand = cmd

cnn.Open()

adpSQLS.Fill(dst, strdtbName)

cnn.Close()

adpSQLS.Dispose()

cmd.Dispose()

cnn.Dispose()

Return dst.Tables(strdtbName)

Catch expSql As SqlException

Windows.Forms.MessageBox.Show(expSql.tostring)

End Try

Adam Machanic
2/5/2005 5:53:37 PM

It sounds like you might have some blocking in your query. You can use
Profiler to watch for lock timeouts and try to fix it, or you can
investigate either increasing ADO.NET's timeout, or utilizing dirty reads in
your queries using the READ UNCOMMITTED isolation level. The first option
is probably best since it fixes the problem at the source, but sometimes
dirty reads are the only way around complex locking issues in very heavily
utilized databases... But as I don't know your application domain I don't
know if that's an appropriate choice...

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

[quoted text, click to view]

sql server data mining : SQL Timeout Error