0

Preferably without editing the stored procedure, is it possible that the output messages are displayed after every stored procedure completes execution, instead of at the very end?

For example, the following script (that calls an SP to insert 300K+ rows per day) will display the 4 output messages (300230 rows affected) once all SPs execute or if the process is cancelled.

exec usp_UpdateSales '11/21/2022'
exec usp_UpdateSales '11/22/2022'
exec usp_UpdateSales '11/23/2022'
exec usp_UpdateSales '11/24/2022'

Instead of showing the output messages at the end, is it possible that is displays the messages after the execution of each call?

I was reading about RAISERROR, but I'm not certain how to implement it into an SP call.

fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110
  • Do all procedures need to run as part of the same batch? if you run as separate batches you'll get the output at the end of each batch. e.g. in [this quick example](https://i.stack.imgur.com/3jdSG.png) you can see the message from the first call while the 2nd call is still executing – GarethD Feb 15 '23 at 12:02
  • Are you referring to the rows affected messages in the SSMS results tab? Those are generated and controlled by SSMS when `SET NOCOUNT OFF` is effective. SSMS will display those and other messages returned by the proc at the end of each batch so you can add `GO` batch separators after each exec. – Dan Guzman Feb 15 '23 at 12:10
  • 3
    You can also flush the message buffer by adding `RAISERROR('',0,0) WITH NOWAIT;` after each exec. – Dan Guzman Feb 15 '23 at 12:13
  • 1
    The `rows affected` messages are output after each statement (unless `SET NOCOUNT ON` is effective, in which case they're only output after `select` statements that return a result set). Probably you're observing SSMS behavior where it only switches to the Messages tab when the batch execution is complete. In a .NET program where you've hooked the `SqlConnection`'s [`InfoMessage` event](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.infomessage) you'll receive the messages as soon as they're output. – AlwaysLearning Feb 15 '23 at 12:18

1 Answers1

0

The SSIS Execute SQL Task does not hook the InfoMessage event. What you want from a problem statement, is to capture the messages printed to the screen in other applications.

As @AlwaysLearning points out, the approach is to create your own Script Task that runs your statement and then "does the thing" with the feedback from your stored procedure https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.infomessage

Some related sample bits of code to give you a solid shove in the right direction

billinkc
  • 59,250
  • 9
  • 102
  • 159