Request timed out while trying to execute short procedure

Question

Here is my connection string:

Global Const strConn As String = _ "PROVIDER=SQLNCLI10;" & _ "P-SSWORD=blahblah;" & _ "USER ID=blahblah;" & _ "INITIAL CATALOG=blah;" & _ "DATA SOURCE=blah;" & _ "CONNECT TIMEOUT=0;" & _ "COMMAND TIMEMOUT=0" & _ "PACKET SIZE=4096;" 

And here is a simple code:

 Sub MoveDataUsingADO() Dim cn As Object Dim cm As Object Dim rs As Object 'get in touch with the server 'Create ado objects. Set cn = CreateObject("ADODB.Connection") cn.connectiontimeout = 0 cn.Open strConn cn.CommandTimeout = 0 Set cm = CreateObject("ADODB.Command") Set cm.ActiveConnection = cn cm.CommandType = 4 'adCmdStoredProc Set rs = CreateObject("ADODB.Recordset") Set rs.ActiveConnection = cn cm.CommandText = "WH.dbo.ourProcName" With wb.Sheets("Data") .Activate .Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 2).End(Excel.xlUp).Row + 1, .Cells(1, .Columns.Count).End(Excel.xlToLeft).Column)).ClearContents End With With rs .Open Source:=cm.Execute '<=====errors here=========== wb.Sheets("Data").Cells(wb.Sheets("Data").Rows.Count, 1).End(Excel.xlUp)(2, 1).CopyFromRecordset rs .Close 'close connection End With ... ... 

At the point above, I get the following error:

I don’t understand - the procedure takes 55 seconds, and throughout my vba I set the timeouts to 0 ... I thought this caused them to not expire?

Answer 1

Did you try to set the timeout explicitly on the team?

 cm.CommandTimeout = 0 

Instead of setting it to a connection. I think the connection timeout deals with how long it tries to establish the connection before it rejects, while the command timeout is how long you have to execute the command.

Answer 2

Do you have any idea how long your request should take? Could there be another problem besides the waiting period?

You should try to set a timeout on the command object as follows:

 cm.CommandText = "WH.dbo.ourProcName" cm.CommandTimeout = 120 With wb.Sheets("Data") .Activate .Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 2).End(Excel.xlUp).Row + 1, .Cells(1, .Columns.Count).End(Excel.xlToLeft).Column)).ClearContents End With With rs .Open Source:=cm.Execute '<=====errors here=========== wb.Sheets("Data").Cells(wb.Sheets("Data").Rows.Count, 1).End(Excel.xlUp)(2, 1).CopyFromRecordset rs .Close 'close connection End With