excel - 尝试运行短过程时查询超时已过期

Question

这是我的连接字符串:

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;"

这是简单的代码:

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

在上面标记的点上，我收到以下错误:

我不明白 - 该过程需要 55 秒才能运行，并且在我的 vba 中，我已将超时设置为 0...我认为这迫使他们 < em>没有过期？

Answer 1

您是否尝试过在命令上明确设置超时？

cm.CommandTimeout = 0

而不是在连接上设置它。我认为连接超时涉及它让您在拒绝之前尝试建立连接的时间，而命令超时是您命令必须执行的时间。