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.
1条答案
按热度按时间hkmswyz61#
The SSIS
Execute SQL Taskdoes 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