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

Norman Yuan
10/29/2007 3:16:45 PM

Ther are two types of timeout exception that could be raised by SqlClient
objects: SqlConnection and SqlCommand.

SqlConnection timeout exception is raised when app is trying to establish
connection but not successful within the given time period, which, in your
case, is 500 (more than 8min! You do not need that long time to be convinced
there is connection problem!).

SqlCommand's Timeout decides how long time a command is given for the
application that uses the SQlCommand to wait for SQL Server to finish the
operation invoced by the SqlCommand. It seems the SqlCommand in your
application starts a lengthy data processing in SqlServer, so you should set
longer timeout to the SqlCommand object (30 is default), not SqlConnection
object. However, 500 seems too much. if the process needs that much time to
run, you'd better rethink your app logic, for example, break data into
smaller chunks, then process it one by one...

[quoted text, click to view]

Rick
10/29/2007 4:56:24 PM

We have a several vb.net apps, that were recently upgraded from Framework
1.1 to Framework 2.0. Randomly these apps have started throwing the error
below, the line of code is a connection.open, the connection string has a
timeout of 500, which should be plenty of time. All of the apps use the same
data layer class (happens on the same line of code). It doesn't happen every
time, it's just random and it happens on the connect open not when executing
the command(an insert into a large table).

Anyone have any suggetions?

The Source was: .Net SqlClient Data Provider

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

Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PSXSTL.Digecenter.LookupDataReference.insertDB() in
C:\$\Digecenter\PSXSTL.Digecenter\Archives\LookupDataReference.vb:line 558

Target Site: Void OnError(System.Data.SqlClient.SqlException, Boolean)

Additional Info: Error occurred in
PSXSTL.Digecenter.LookupDataReference/insertDB
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PSXSTL.Digecenter.LookupDataReference.insertDB() in
C:\$\Digecenter\PSXSTL.Digecenter\Archives\LookupDataReference.vb:line 558

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

Thanks in advance!
Rick

v-wywang NO[at]SPAM online.microsoft.com
10/30/2007 12:00:00 AM

Hello Rick,
Thanks for Norman's suggestion.

I noticed the exception was raised by SqlCommand.ExecuteNonQuery() method.
Stack Trace:
...
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Have you tried increasing the SqlCommand.Timeout property?
The default value of SqlCommand is 30 (30 seconds).
It seems this process need much more time to run. Maybe you should increase
the timeout of this command.

Hope this helps.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Rick
10/30/2007 11:43:40 AM

The command timeout is set to 120 and the connection timeout is 500, the
command is executing a stored proc that inserts 1 record. The stored proc
works as expected, it will return 'SUCCESS' on insert or the error message
from the stored proc if it failed. As I said, It only happens randomly. And
it happens on the _cn.open

Private _cn As System.Data.SqlClient.SqlConnection

_cn = New SqlConnection(connectionstring here)

strSQL.Append("EXEC usp_InsertTblLookupDataReference ")

strSQL.Append(PARAMETERS HERE)

If Not _cn.State = ConnectionState.Open Then

_cn.Open()

End If

Dim returnValue As String = ""

countParam = New SqlClient.SqlParameter("@insertSuccess",
SqlDbType.NVarChar, 4000)

localSQL = New System.Data.SqlClient.SqlCommand(strSQL.ToString, _cn)

localSQL.CommandTimeout = 120

countParam.Direction = ParameterDirection.Output

localSQL.Parameters.Add(countParam)

localSQL.ExecuteNonQuery()

returnValue = localSQL.Parameters("@insertSuccess").Value.ToString

If returnValue.ToUpper <> "SUCCESS" Then

Throw New Exception("Error Inserting TblLookupDataReference record:" &
returnValue & " - " & strSQL.ToString)

End If

If _cn.State = ConnectionState.Open Then

_cn.Close()

End If

[quoted text, click to view]

Cor Ligthert[MVP]
10/31/2007 12:00:00 AM

Rick,

Are you sure that there is no "Transaction" used in a class, which is not
committed or not rolled back?

This is in my idea the first point to look for if there are "Transactions"
used.

Cor

William Vaughn
10/31/2007 9:41:49 AM

This could be caused by a transient deadly embrace or other locking issue.
It might also be caused by someone using Reporting Services on the same
system, or another service that hogs disk IO channels or consumes more than
its share of CPU time. It could be something as simple as SQL Server
building a new extent as new data pages are added.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

[quoted text, click to view]

Rick
10/31/2007 11:09:54 AM

I'm positive it's not using Transactions.

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
11/2/2007 12:00:00 AM

Hello Rick,

Thanks for William's suggestion. :) This error also can either be that SQL
Server was too busy to respond or we didn't get a connection back from SQL
Server fast enough due to networking issues, etc. Can you successfully
connect to SQL Server with other applications at the time of the failure?

Hope this helps.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

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