Timeout expired for executing ms-sql store procedure?

I receive time out error message for executing ms-sql store procedure message (Timeout expired -2147217871) using ADODB.Connection

How to I increase Time Out interval?
AStronusAsked:
Who is Participating?
 
gallo47Connect With a Mentor Commented:
This is the code I use for stored procedures:

Dim cn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;User ID=uid;Password=pw;Initial Catalog=dbname;Data Source=server"

Set cmd = New ADODB.Command

With cmd
     .ActiveConnection = cn
     .CommandText = "usp_SPName"
     .CommandType = adCmdStoredProc
     .Parameters("@Param1") = SomeValue
     .CommandTimeout = 300  'This is where the timeout is set
End with

Set RS = cmd.Execute
0
 
itmeloCommented:
For a Connection:
oConn.ConnectionTimeout = 30
For a Command:
oCmd.CommandTimeout = 30
0
 
gallo47Commented:
Sorry itmelo beat me to it
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AStronusAuthor Commented:
I'm using ConnectionString
and set
oConn.ConnectionString="...."
oConn.ConnectionTimeout=900000000
oConn.Open
recordset=OConn.Execute("....")
but I still receive Time out
:(
0
 
Anthony PerkinsCommented:
Set the CommandTimeout not the ConnectionTimeout property.  In other words:
oConn.ConnectionString="...."
oConn.CommandTimeout = 0  'Infinite timeout
oConn.Open
recordset=OConn.Execute("....")

Anthony
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the stored procedure times out, you might first check how much time it needs to execute (using query analyser). If there it takes only seconds, then you need to check why the connection takes so much time, otherwise start optimizing the proc

CHeers
0
 
AStronusAuthor Commented:
ConnectionTimeout doesn't work for me, as I change ConnectionString to ADODB.Command and set Time out it's work great.  Sorry, all answer is correct but I can give pts to 1 person only.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.