Time out errors

[Rob]

VB.net 2005 Windows app... makes calls to 2005 SQL server... sometimes I get
a Timeout error, other times I do not...

What is the best way to handle timeout errors ?


1. How to increase the timeout - from the app it appears that the property
is "read -only" ? So must it be set at the server from within SQL
Management studio ? If so how ?

2. How would you implement a "try again" method ?

Thanks !

 

[Mr. Arnold]

VB.net 2005 Windows app... makes calls to 2005 SQL server... sometimes I
get a Timeout error, other times I do not...

What is the best way to handle timeout errors ?


1. How to increase the timeout - from the app it appears that the property
is "read -only" ? So must it be set at the server from within SQL
Management studio ? If so how ?

Maybe, you have too many connections open, because things are not being
closed properly. The application cannot get another connection and is timing
out, because of existing open connections that were never disposed.

2. How would you implement a "try again" method ?

Maybe, you don't want to try again and find out what the problem is, because
it shouldn't be happening.

 

[Rob]

Hi,

The code goes something like this.... I am opening, closing, disposing,
then opening, closing, disposing, again.... maybe as many as 10 times in a
row back to back.... could this be a problem ?

cnn = New SqlConnection(strConnection)
cnn.Open

Dim cmd as New SqlCommand
cmd = cnn.CreateCommand
' code to execute a stored proc
cmd.ExecuteNonQuery()

cnn.close()
cnn.Dispose()

cmd.Dispose()
cmd=nothing

cnn = New SqlConnection(strConnection)
cnn.Open

Dim cmd2 as New SqlCommand
cmd2 = cnn.CreateCommand
' code to execute a stored proc
cmd2.ExecuteNonQuery()

cnn.close()
cnn.Dispose()

cmd2.Dispose()
cmd2=nothing

Thanks !

 

[Mr. Arnold]

Hi,

The code goes something like this.... I am opening, closing, disposing,
then opening, closing, disposing, again.... maybe as many as 10 times in a
row back to back.... could this be a problem ?

cnn = New SqlConnection(strConnection)
cnn.Open

Dim cmd as New SqlCommand
cmd = cnn.CreateCommand
' code to execute a stored proc
cmd.ExecuteNonQuery()

cnn.close()
cnn.Dispose()

cmd.Dispose()
cmd=nothing

cnn = New SqlConnection(strConnection)
cnn.Open

Dim cmd2 as New SqlCommand
cmd2 = cnn.CreateCommand
' code to execute a stored proc
cmd2.ExecuteNonQuery()

cnn.close()
cnn.Dispose()

cmd2.Dispose()
cmd2=nothing

Thanks !

I can't say that it is. What you need to do is go to SQL Server 2005 using
the Query Analyzer equivalent that can be used on SQL Server 2000 and use
the the SQL Server Stored Procedure utility (I can't think of the Store
Procedure name) that tells you the active user-id(s) that are accessing SQL
server.

If you run your program and use that SQL Server utility, and you you see the
user-id the application is using to access SQL Server more than one time,
then you have a problem. The user-id should be showing only once throughout
the running of said application.


If this is in-line code back to back as you say, then you open the
connection one time, you execute your SQL commands, close the connection and
dispose of the connection. One connection that stays open until you execute
all the commands and then close the connection.

You use the open and close method when in-line back to back code is not
being used, with SQL Server access being done in different areas of the
code, like in different subroutines being called, as an example with their
own code to access SQl Server.
..

 

[Guest]

if you use vs.NET 2005 you would better use a using construct


Using cnn As New SqlConnection

Using cmd As New SqlCommand

End Using



End Using

 

[Rob]

Using the Activity monitor in SQL 2005, it looks like it is creating more
than one connection per user... it appears that the Dispose may not actually
killing the process id... any idea why ?

 

[Guest]

another thingy i noticed

You :

cnn = New SqlConnection(strConnection)
cnn.Open

Dim cmd as New SqlCommand
cmd = cnn.CreateCommand
' code to execute a stored proc
cmd.ExecuteNonQuery()

cnn.close()
cnn.Dispose()

cmd.Dispose()
cmd.Dispose()
cmd=nothing

I would do it like this : ( when i used VS 2003 or < )

cnn = New SqlConnection(strConnection)
cnn.Open

Dim cmd as New SqlCommand
cmd = cnn.CreateCommand

' code to execute a stored proc
cmd.ExecuteNonQuery()

cnn.close()




As the command is a child of the connection , maybe because you use the
wrong order the resources are not properly released ?

 

[Mr. Arnold]

Using the Activity monitor in SQL 2005, it looks like it is creating more
than one connection per user... it appears that the Dispose may not
actually killing the process id... any idea why ?

No I don't know why. Maybe you just need to use the ADO.NET USING statement
instead.

http://www.pluralsight.com/blogs/fritz/archive/2005/04/28/7834.aspx

Like I said, if all this is back to back SQL access with in-line code
statements in one procedure, then you only need one connection statement
and one cnn.Open for all of it. And you close it when done or use the USING
statement.