vba 尝试运行简短程序时查询超时已过期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19758773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query timeout expired when trying to run a short procedure
提问by whytheq
Here's 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 the 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 marked above I get the following error:
在上面标记的点上，我收到以下错误：
I don't understand - the procedure takes 55 seconds to run and throughout my vbaI have set timeouts to 0... I thought this forced them to notexpire?
我不明白 - 该程序需要 55 秒才能运行，并且在我的整个过程中我vba已将超时设置为0......我认为这迫使它们不会过期？
回答by Simon1979
Have you tried setting the time out explicitly on the command?
您是否尝试过在命令上明确设置超时？
cm.CommandTimeout = 0
Rather than setting it on the connection. I think the connection timeout deals with how long it lets you try to establish a connection before it rejects while the command timeout is how long you command has to execute.
而不是在连接上设置它。我认为连接超时处理的是它让您在拒绝之前尝试建立连接的时间，而命令超时是您命令必须执行的时间。
回答by HK1
Do you have some idea how long it should take your query to execute? Might there be a different problem than the timeout period?
您知道执行查询需要多长时间吗？可能存在与超时期限不同的问题吗？
You should try setting the timeout on the command object like this:
您应该尝试在命令对象上设置超时，如下所示：
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
