I have been running up a stored procedure that runs several separate update and insert statements. It also prints some status messages that I can see in sql management studio. I am going to automate this procedure to run overnight but need the output as a final check to be sent to me.
I know how to use the email stored procedure and have that working but can't find where the message output i.e. (10 rows affected)... is all stored.
you can always store the output in a table
exec master..xp_sendmail
@recipients = '[email protected]',
@subject = 'my alert'.
@query = 'select * from mytable'
Informational messages like '10 rows affected' or PRINT output are sent straight to the client and you can't capture it in Transact-SQL. You can, however, be the client if you are a CLR procedure, and in that case you will get all the output from the procedure you invoked. Requires SQL Server 2005 or newer for CLR.
A much better alternative is to have the procedure create a true output, like a rowset result or output parameters.