Return Warning from SQL Server to VBA via ADODB Connection

I am trying to generate a warning in a SQL Server store procedure that is returned to VBA via an ADODB connection. If I use RAISEERROR with a severity of 11 or higher, the error is returned to VBA but the stored procedure aborts. If I use a severity of 10 or lower, the stored procedure does not abort but the "error" is not returned to the VBA caller.

How can I generate an "error" that is returned to the caller via the ADODB connection object without forcing the stored procedure to abort?

Kevin
LVL 81
zorvek (Kevin Jones)ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
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

Open 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)
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
I tried that but they are not included in the errors collection unless a fatal error occurs.

Kevin
0
MlandaTCommented:
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 :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zorvek (Kevin Jones)ConsultantAuthor Commented:
I'm using VBA in Excel.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
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
0
MlandaTCommented:
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

Open 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?
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
They are in the CATCH part of a TRY..CATCH block.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
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.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
After a number of tests I have concluded that there is no way to return both an error in the Errors collection AND a recordset at the same time using an ADODB connection object.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
ATM?
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
The answer, as far as I can tell, is that the objective cannot be realized.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.