sql server - How to retrieve all errors and messages from a query using ADO

Question

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?

If I run this script:

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

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

Is there a way to get the remaining messages?

Answer 1

I figured it out on my own.

This works:

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