selecting the message output of a SP into a variable

[kittyyo]

Hi all!

I've stumbled over a new problem and have not found anything yet on the Internet:

I call a stored procedure (by another vendor) this way:

DECLARE @return_value int,
@AnzahlBenutzer int,
@BenutzerSendefehler int,
@Fehlerprotokoll nvarchar(max)

EXEC @return_value = [dbo].[pl_VersendeBenutzer]
@AnzahlBenutzer = @AnzahlBenutzer OUTPUT,
@BenutzerSendefehler = @BenutzerSendefehler OUTPUT,
@Fehlerprotokoll = @Fehlerprotokoll OUTPUT

This script outputs the following text under "messages" when I execute it in management studio:

"
row updated
row updated
row updated
row updated
row updated
row updated
"

Is there any way for me to select this message text into a variable inside my script?

Thanks a lot,
Anne

 

[bboffin]

I'm not aware of any way to pick up these messages within the T-SQL as none of the @@ variables seem to be suitable.

However, assuming that you're using ADO.NET you can pick up SQL messages in the code that calls the SP if you create an event handler for the Connection object that deals with the InfoMessage event.

This gets called each time a message is produced.

I'm not sure if you'll get exactly the messages that are shown in Management Studio as it may be applying its own interpretation. You will definitely get all messages produced by PRINT statements which may be useful when debugging your code.



Bob Boffin

 

[kittyyo]

Thanks for your answer!
Unfortunetely I have to use the message output inside a T-SQL stored procedure. However I now have some more clues on how to search on the Internet about this issue.

Anne

 

[kittyyo]

I found this article on the net:

http://www.sommarskog.se/error-handling-I.html

Basically it says that messages with a severity smaller than 11 (I think I'm dealing with this kind here) cannot be trapped directly using T-SQL.

So if none of you experts has a solution to this issue (I'm still hoping!), I will go and ask the vendor to give his messages a severity bigger than 10 if they are error messages.

Thanks,
Anne