Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding..Net SqlClient Data Provider
why we get this error
Rick wrote:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
29-Oct-07
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
Previous Posts In This Thread:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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
Ther are two types of timeout exception that could be raised by SqlClient
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...
Hello Rick,Thanks for Norman's suggestion.
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.
The command timeout is set to 120 and the connection timeout is 500, the
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
Rick,Are you sure that there is no "Transaction" used in a class, which is not
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
I'm positive it's not using Transactions.
I am positive it is not using Transactions.
This could be caused by a transient deadly embrace or other locking issue.
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)
-----------------------------------------------------------------------------------------------------------------------
Hello Rick,Thanks for William's suggestion.
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.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This Artcile may help you out.
http://www.tipsstation.com/article/Timeout-expired-server-is-not-responding-Asp-Dot-Net.aspx
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 4
http://www.eggheadcafe.com/tutorial...45-8b37cb7f3186/wpf-report-engine-part-4.aspx