Timeout expired. The timeout period elapsed prior to completion of the operation or the server is n

R

Rick

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
 
N

Norman Yuan

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...
 
W

WenYuan Wang [MSFT]

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.
 
R

Rick

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
 
C

Cor Ligthert[MVP]

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
 
W

William Vaughn

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)
 
W

WenYuan Wang [MSFT]

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.
 
S

SSNJB VENKI

This is where i get an error
How to solve

cboCustomer.DataSource = DataAccess.GetDataView(strsql)

Pls Help
 
S

SSNJB VENKI

This is where i get an error
How to solve

cboCustomer.DataSource = DataAccess.GetDataView(strsql)

Pls Help
 
W

William Vaughn \(MVP\)

I don't see the root of this message and I don't see how you've built these
classes. GetDataView is not an ADO.NET method AFAIK so I assume it's one
you've built. A timeout exception can be caused by a full connection pool or
by a query that's too complex to execute in the allotted time (30 seconds is
the default). Is this ADO.NET?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
S

sindhu Mayappa

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top