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(SqlExc eption
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PSXSTL.Digecenter.LookupDataReference.insertDB() in
C:\$\Digecenter\PSXSTL.Digecenter\Archives\LookupD ataReference.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(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PSXSTL.Digecenter.LookupDataReference.insertDB() in
C:\$\Digecenter\PSXSTL.Digecenter\Archives\LookupD ataReference.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
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...



"Rick" <rfemmer (AT) newsgroups (DOT) nospam> wrote

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
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.ToStri ng

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

"WenYuan Wang [MSFT]" <v-wywang (AT) online (DOT) microsoft.com> wrote

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.

"Cor Ligthert[MVP]" <notmyfirstname (AT) planet (DOT) nl> wrote

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

"Rick" <rfemmer (AT) newsgroups (DOT) nospam> wrote

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.