Pass progress from multi-step ADO query to VBA using Raiserror: Is this possible?

Question

Preface

I'm making this question specific to conform to SO asking guidelines, but feel free to suggest wholesale redesign if you wish. I may be using some bad practices.

---

Basic Question

I use ADO to execute a multi-step SQL Server query that takes several minutes to execute. I use Raiserror in my tsql queries to let myself know more verbosely which steps have finished. Is it possible to pass these messages to VBA before the complete query finishes, while still continuing with the query?

---

Details and Code

I use the vba below to execute the t-SQL query underneath. As you can see, there are two errors raised in the t-SQL that display "Step 1 complete" and "Step 2 complete". Could I pass these messages (or alternately use error numbers and pass those) back to VBA in a way that would allow me to detect them and update a progress bar while continuing to execute the query?

VBA used to execute the query:

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandTimeout = 0
    cmd.CommandText = strQuery

    Set rst = New ADODB.Recordset
    rst.Open cmd
    'Go to the second to last recordset of the multi-step query
    String1 = Replace(strQuery, ";", "")
    For Loop2 = 1 To (Len(strQuery) - (Len(String1) + 1))
        Set rst = rst.NextRecordset
    Next Loop2

    'Copy results
    If Not rst.EOF Then
        (snip - actions)
    Else
        MsgBox "Error: No records returned."
    End If

Stripped-down piece of multi-step tSQL query:

--#DRS1: The numbers being researched   
select distinct numbers 
into #DRS1
from Table1 (nolock)    
where numbers in ()


--#DRS1: Index
create nonclustered index Idx_DRS1
    on #DRS1(numbers);

Raiserror(“Step 1 complete”,1,1) with nowait;

--#DRS2: Table2 for numbers being researched
select distinct
 DRS1.numbers
,a.ID

into #DRS2

from #DRS1 DRS1

join Table2 (nolock) a  
    on DRS1.numbers = a.numbers

Raiserror(“Step 2 complete”,1,1) with nowait;


--MORE STEPS
(more steps)
(more raiserror statements)

Clarification

I am not interested in:

• A method that doesn't allow me to update a progress bar until the query is completely done.
• A method that uses Progress/MaxProgress, because as I understand it that would return separate numbers for each of the steps in my query, rather than one progress measure for the entire query.

I am less interested in:

• Using # records affected messages to determine progress, because some steps may return equal numbers of records to previous steps.

Research

The closest thing I have found to what I'm looking for is here, but as the discussion of that solution here says:

This approach would only work for stored procedures that are not intended to return results, say procs that insert data into tables. Another approach would be needed if your stored proc returns a result set.

Since I return results in the final step of my query to be manipulated in Excel I don't think this would work for me.

External link code for reference

SQL:

CREATE PROCEDURE dbo.updTesting As

Declare @RetVal integer

Exec @RetVal = updTesting2
Return @RetVal
GO

CREATE PROCEDURE dbo.updTesting2 As

raiserror('Error From Testing 2 procedure',16,1)
Return -2
GO

VBA:

Private Sub Command1_Click()
    On Error GoTo ErrorHandler

    Dim db As ADODB.Connection
    Dim cmd As ADODB.Command

    Set db = New ADODB.Connection
    db.CursorLocation = adUseClient
    db.Open "provider=sqloledb;data source=handel;initial catalog=northwind;integrated security=sspi"

    Set cmd = New ADODB.Command

    With cmd
        Set .ActiveConnection = db
        .CommandText = "updTesting"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue)
        .Execute , , adExecuteNoRecords
    End With

ExitPoint:
    On Error Resume Next
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing

    db.Close
    Set db = Nothing

    Exit Sub

ErrorHandler:
    MsgBox "Error # " & Err.Number & vbNewLine & vbNewLine & Err.Description
    Resume ExitPoint
End Sub

Answer 1

There are several possibilities to work out a solution for your problem:

(1) Capture the error messages as they occur while the query is running. That's the requested approach.

(2) Break-down the big, long query into several smaller chunks and run them one after the other. Like this you know which part is completed and you can update your progress bar based on that information just before sending the next chunk to the server.

(3) Update the big, long query to log its progress on the server in a temp table and then read out this log while the other query is still running.

While I'd recommend to use errors only when errors occur and not to "abuse" them for logging, tracking, or feedback, both options (1 & 2) are quite feasible with events:

Similar to Worksheet events Worksheet_Change, Worksheet_Activate, or Worksheet_BeforeDoubleClick there are also ADODB events for ADODB.Connection and ADODB.Recordset. Both are well documented and can be easily viewed within the VBE by (1) adding a reference to Microsoft ActiveX Data Objects x.x Library (2) pressing F2 (3) selecting the ADODB library in the drop-down menu at the top (4) and finally looking up Recordset or Connection within the classes. Here are the available events for Connection:

As you can see, all events are marked with a lightning. To capture / use these events you need to create a Class Module in the VBE and add the following line to it:

Dim WithEvents adoConnection As ADODB.Connection

Afterwards, you can make use of the newly created ADODB.Connection event and select the required event from the top of the list:

The applicable event for option (1) is the InfoMessage event which occurs "[...] whenever a warning occurs during a ConnectionEvent operation." The import part here is during a connection. So, this event fires automatically whenever an ADODB connection "gets" an error.

Of course, this means that the original query to the server must be sent without waiting for an answer. Instead you should use the above event to capture any errors while the query is executing and create yet another event to automatically fire when the entire query completed.

For some more help in respect to asynchronous ADODB connection and possible problems with them you might want to have a look at the following two posts here:

ExecuteComplete ADODB Connection event not fired with adAsyncExecute parameter

Running multiple async queries with ADODB - callbacks not always firing

A similar approach can be used with option (3) as described above and asynchronous ADODB connections.

Let me know if this solves your problems or if you have any further questions.

_{All available ADODB events can be reviewed here https://msdn.microsoft.com/en-us/library/ms675083%28v=vs.85%29.aspx}