Retrieve the Message part of a stored proc?

Question

Retrieve the Message part of a stored proc?

voyager_87

SSC Enthusiast

Points: 197
More actions
October 14, 2011 at 7:30 am

#390622

I know how to retrieve data from a stored procedure using VBA in MS Access, but is there a way to retrive the message part fo the Stored Procedure? When I execute my stored procedure in SSMS it returns the result in the "Results" tab, but I also have print statements throughout the procedure to tell the user what step is being processed. Those comments appear in the "Messages" tab in SSMS. How can I retrieve these messages and return them to an application in MS Access using VBA. I am calling the procedure in ADO, but I don't see anything in ADO that allows me to reference the messages. Any push in the right direction would be greatly appreicated.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

the Connection Object in ADODB that you create has an Errors collection, which includes the print statements, "rows affected" as well as anytime you called RAISERROR i believe.

print statemetns are error 0 messages.

see this link from Microsoft for a code example:

http://msdn.microsoft.com/en-us/library/aa905919(v=sql.80).aspx

or you can google "ADODB messages" or "ADODB print statements"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

voyager_87 SSC Enthusiast Points: 197 More actions · Answer 2

Thanks for the tip and for replying so quickly! I'll check that out and let you know the results.

voyager_87 SSC Enthusiast Points: 197 More actions · Answer 3

The error messages seem to be picking up the data I wanted to capture. However, now I have the problem of trying to actually add the data to a listbox that I placed on the form as the example you passed to me shows. It does not like the lstListBoxName.AddItem command. The AddItem command does not even show up in the VBA editor for the list box. I'm using Access 2000 with the ActiveX Data Object 2.1 Library attached. I know this is now off the topic of SQL Server, but do you have any suggestions on that step?

voyager_87 SSC Enthusiast Points: 197 More actions · Answer 4

I think I answered the second part of my own question. AddItem was not added until version 2003. I need to use the Rowsource property in Access 2000. See the attached link...

http://us.generation-nt.com/answer/add-an-item-listbox-vba-help-16474812.html

Thanks for you help!