?
Solved

Return Warning from SQL Server to VBA via ADODB Connection

Posted on 2014-08-29
14
Medium Priority
?
803 Views
Last Modified: 2014-09-04
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
0
Comment
Question by:zorvek (Kevin Jones)
  • 9
  • 3
  • 2
14 Comments
 
LVL 31

Expert Comment

by:MlandaT
ID: 40293782
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40293785
I tried that but they are not included in the errors collection unless a fatal error occurs.

Kevin
0
 
LVL 31

Expert Comment

by:MlandaT
ID: 40293792
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40293797
I'm using VBA in Excel.
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40293803
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
 
LVL 31

Expert Comment

by:MlandaT
ID: 40293832
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40293837
They are in the CATCH part of a TRY..CATCH block.
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40293841
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 0 total points
ID: 40294902
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 40295852
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40295881
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 40295886
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 40295887
ATM?
0
 
LVL 81

Author Closing Comment

by:zorvek (Kevin Jones)
ID: 40302985
The answer, as far as I can tell, is that the objective cannot be realized.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question