sql-server - SQL 存储过程在 Access 中超时

Question

从 Access VBA 执行时，以下 SP 超时。从管理控制台执行时它工作正常。如果相关，服务器的 ping 时间确实很长:

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param As New ADODB.Parameter
Dim fld As ADODB.Field
Dim stMessage As String


Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=server01;DATABASE=db_a;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc

    cmd.CommandText = "we_ci_db.stprUpdateValuesInitial_A"

    Set param = cmd.CreateParameter("@DataSet", adInteger, adParamInput, , stDataSet)
    cmd.Parameters.Append param

    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic

    Set rs = cmd.Execute


exit1:
Set cmd = Nothing
Set rs = Nothing
Exit Function

Answer 1

您可以使用 ADODB.Command 对象的 CommandTimeout 属性设置超时:

// set a very high number of seconds before timeout
cmd.CommandTimeout = 9999999