Timeout executing SQL Server stored proc

[Phil Hellmuth]

I'm attempting to run the following code. A sproc is called (a lengthy
one), and after approx. 1 minute I get a 'timeout expired' error message,
even though I've tried various values for the connection's ConnectionTimeout
parameter. Any suggestions?

Dim adoConnection As ADODB.Connection

Const sDataSource As String = "ThisDB"

Const sUsername As String = "uid"

Const sPassword As String = "ABC"

Set adoConnection = New ADODB.Connection

adoConnection.ConnectionTimeout = 0

adoConnection.Open "DSN=" & sDataSource, sUsername, sPassword

Dim adoCommand As New ADODB.Command

adoCommand.ActiveConnection = adoConnection

adoCommand.CommandText = "LongSproc"

adoCommand.CommandType = adCmdStoredProc

adoCommand.Execute

 

[Mike Labosh]

I'm attempting to run the following code. A sproc is called (a lengthy

one), and after approx. 1 minute I get a 'timeout expired' error message,
even though I've tried various values for the connection's ConnectionTimeout
parameter. Any suggestions?

Yes, this is really confusing

The ConnectionTimeout property specifies how long to wait while calling the
connection's Open() method. For what you're trying to do, set the
CommandTimeout property of your Command object. Check this out:

Dim adoConnection As ADODB.Connection
Const sDataSource As String = "ThisDB"
Const sUsername As String = "uid"
Const sPassword As String = "ABC"

Set adoConnection = New ADODB.Connection

' This tells the Open() method to fail if it takes longer
' than 60 seconds to connect:

adoConnection.ConnectionTimeout = 60

adoConnection.Open "DSN=" & sDataSource, sUsername, sPassword

Dim adoCommand As New ADODB.Command

' This tells the command to wait 5 minutes for the command
' to complete before failing the command:

adoCommand.CommandTimeout = 300

adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "LongSproc"
adoCommand.CommandType = adCmdStoredProc
adoCommand.Execute

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~