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

Posted by Johan Levin on Stack Overflow See other posts from Stack Overflow or by Johan Levin
Published on 2010-06-10T10:13:57Z Indexed on 2010/06/10 11:23 UTC
Read the original article Hit count: 251

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about vbscript