sql-server - 尝试从 VBA 运行 SQL 脚本

Question

我希望运行一些访问 SQL Server、运行 SQL 脚本并返回输出的 VBA 代码。

我的脚本适用于较小的 SQL 脚本，但是当我运行大约需要 3 分 30 秒的 SQL 脚本时，我得到：

运行时错误'-2147217871 查询超时已过期。

这是我第一次尝试这样做，所以我并不完全了解我的代码中发生的一切。

这是我使用的 VBA 代码：

Dim oConn As ADODB.Connection
Dim objCmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim mssql As String
Dim row As Integer
Dim col As Integer
Dim wb As Workbook
Set wb = ThisWorkbook
Application.ScreenUpdating = False


Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset


'CREATE A STRING BY READING THE SQL FILE ROW BY ROW
mssql = ""

Dim hnd As Integer
hnd = FreeFile

'paste the place where the sql file is in here
Open "directory for my sql file" For Input As hnd

'loop sql file row by row and add each row to the string
Dim file_row As String
Do Until EOF(hnd)
    Line Input #hnd, file_row
    mssql = mssql & file_row & vbNewLine
Loop

Close #hnd


'Debug.Print mssql

oConn.ConnectionString = "driver={SQL Server};" & _
"server=myserver;uid=name;pwd=password;database=my database"

'-------------changing connection timeout and commandTimeout doesn't seem to help--------------'
oConn.ConnectionTimeout = 500
oConn.Open
objCmd.CommandTimeout = 500
objCmd.ActiveConnection = oConn

rs.Open mssql, oConn

If rs.EOF Then
    MsgBox "No matching records found."
    rs.Close
    oConn.Close
    Exit Sub
End If

Debug.Print "We made it"

rs.Close

oConn.Close

标签： sql-servervbadatabase-connection