vba - 当我尝试在 VBA 中执行 SQL View 时，查询超时(运行时错误 - '2147217871')？

Question

下面的 VBA 是我正在研究的，我得到了一个

Query Timeout Expired error (Run time error - '2147217871')

当我尝试执行以下代码时:

Sub ConnectSqlServer2()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=adhoc23;" & _
                  "Initial Catalog=database1;" & _
                  "Integrated Security=SSPI;"


    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM SQLViewName;")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(CC).Range("E5:G34").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    End Sub

请让我知道如何解决超时过期错误。

Answer 1

这取决于您在相关表中有多少记录以及 where 子句中有什么(如果您在 View 中有任何记录)。那可能是您缺少一些索引。

如果您缺少索引，请使用:

CREATE NONCLUSTERED INDEX [Ix_name] ON [dbo].[related table] 
(
    [column1] ASC, [column2] ASC....
)

这样您就可以延长超时时间:

……

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim objCmd As New ADODB.Command   

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=adhoc23;" & _
              "Initial Catalog=database1;" & _
              "Integrated Security=SSPI;"


' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
' Open the connection and execute.
conn.Open sConnString

objCmd.CommandText = "SELECT * FROM SQLViewName"  
objCmd.CommandType = adCmdText 
objCmd.CommandTimeout = 120 'seconds

' Connect to the data source.  
objCmd.ActiveConnection = conn

' Execute once and display...  
Set rs = objCmd.Execute  

' Check we have data.
If Not rs.EOF Then

……