如何使用 ADO 从查询中检索所有错误和消息

2022-01-01 00:00:00 sql-server vbscript ado

当一个 SQL 批处理返回多个消息时，例如打印语句，那么我只能使用 ADO 连接的 Errors 集合检索第一个.我如何获得其余的消息?

When a SQL batch returns more than one message from e.g. print statements, then I can only retrieve the first one using the ADO connection's Errors collection. How do I get the rest of the messages?

如果我运行这个脚本:

Option Explicit
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB"
conn.ConnectionString = "Data Source=(local);Integrated Security=SSPI;Initial Catalog=Master"
conn.Open

conn.Execute("print 'Foo'" & vbCrLf & "print 'Bar'" & vbCrLf & "raiserror ('xyz', 10, 127)")

Dim error
For Each error in conn.Errors
    MsgBox error.Description
Next

然后我只会得到Foo"，而不是Bar"或xyz".

Then I only get "Foo" back, never "Bar" or "xyz".

有没有办法获取剩余的消息?

Is there a way to get the remaining messages?

推荐答案

我自己想出来的.

这有效:

Option Explicit
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB"
conn.ConnectionString = "Data Source=(local);Integrated Security=SSPI;Initial Catalog=Master"
conn.Open

Dim rs
Set rs = conn.Execute("print 'Foo'" & vbCrLf & "print 'Bar'" & vbCrLf & "raiserror ('xyz', 10, 127)")

Dim error
While not (rs is nothing)
    For Each error in conn.Errors
        MsgBox error.Description
    Next
    Set rs = rs.NextRecordSet
Wend

相关文章