Return Warning from SQL Server to VBA via ADODB Connection

In ADO, the output from PRINT statements is populated in the Errors collection, which you can loop through:

Dim msg
For Each msg In dbConnection.Errors
    Response.Write e.Description
Next

Select allOpen in new window

You'll probably be wanting to view the messages in real time though... so look here http://support.microsoft.com/kb/194792

In ADO.NET it is event driven through the SqlInfoMessageEventHandler (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlinfomessageeventhandler%28v=vs.110%29.aspx, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24420)

I tried that but they are not included in the errors collection unless a fatal error occurs.

Kevin

And are the .NET alternatives totally out of the question? I've found it easier to achieve in .NET (though I sometimes hadd issues with the PRINT buffering messages and giving them in one big block at the end). Other than that, umm.. my ADODB is now really rusty :)

I'm using VBA in Excel.

I'm currently trying to use the XACT_ABORT switch but that doesn't seem to stop the stored procedure from aborting:

                  SET XACT_ABORT OFF
                  RAISERROR ('Warning message', 11, 1)
                  SET XACT_ABORT ON

Kevin

What happens if you put the RAISEERROR in TRY..CATCH blocks? Does the Error still get passed to the application?

Also, is this how you are processing your error messages on the VBA side (from the MS website link I gave you earlier):

      Do While (Not ADORs Is Nothing)
          If ADORs.State = adStateClosed Then Exit Do
          While Not ADORs.EOF
              For i = 0 To ADORs.Fields.Count - 1
                  rStr = rStr & " : " & ADORs(i)
              Next i
              Debug.Print Mid(rStr, 3, Len(rStr))
              ADORs.MoveNext
              rStr = ""
          Wend
          Debug.Print "----------------------"
          Set ADORs = ADORs.NextRecordset
      Loop

Select allOpen in new window

If attempting to use this to process PRINT and RAISEERROR messages does not yield results, what if we try to issue example SELECT "This has happened" AS Message and treating this as a stored procedure that just returns multiple result sets? So long as the ADORs.State <> adStateClosed... can we not keep consuming the results like that?

They are in the CATCH part of a TRY..CATCH block.

Actually, the SP does continue executing, but the recordsets are not being returned. When I run the query in SSMS everything works as desired: I get the warnings/errors AND the recordset. But the recordset is not returned the caller when called from VBA using ADODB.

You've always the option to use MARS (Multiple Active Result Sets) - the first result set is the usual one, the second a status info.

We considered that. We also considered return parameters. The problem is that we have a toolkit already built and in use by hundreds of applications and thousands of stored procedures. And many of those stored procedures are already designed to return multiple recordsets. The toolkit is coded to look at the Errors collection and take appropriate action.

The bottom line is we're kinda screwed on this one. Fortunately, we only have a select few instances where we need a warning versus an error with an aborted stored procedure so we are circumventing the deficiency with preflighting--implementing stored procedures to check for warning conditions outside of the stored procedures that actually do the work,

All that said, I'm still considering an Nth recordset in the event that a warning or other status information is needed to be sent back to the caller. The recordset schema would have to be unique so that it can be identified and pulled out of the normal set of data recordsets. Since a recordset can't be named, it would have to be identified by a unique set of field names that would never occur in normal operation.

Kevin

Agree, then you are stuck ATM. Adding a unqiue resultset still seems the best way to handle for the future, but of course requires to rewrite the toolkit, all as you said. "You can't do that" is indeed the short-term answer.

ATM?

The answer, as far as I can tell, is that the objective cannot be realized.