Thanks All, The solutions are working fine. Thanks for your inputs.
Do we have an option incase if we are not scheduling them in SQL job ? I mean if we execute a stored procedure normally in SSMS, can we get those messages and store them in a table ??
Tabular results, errors and and info messages are three different data streams coming out of SQL Server. We take handling tabular results for granted, an INSERT...SELECT statement is an example of handling tabular results in a T-SQL context. We can react to the error stream using TRY/CATCH. To my knowledge however there is no way to react/handle items on the info message stream from within a T-SQL context...it is always output to the client un-touched.
The info messages stream is exposed to all the SQL Server data access drivers which is why SQL Server Agent can capture it, as could any app using the Native Client, OLE DB or ODBC driver. SSMS uses the Native Client to talk to SQL Server.
If you're sending your results to Grid (Press Ctrl+D in your query windows before running a query to switch to Grid results) SSMS displays messages from the error and info streams in the "Messages tab".
If you're sending your results to Text (Press Ctrl+T in your query window before running a query to switch to Text results) SSMS displays all three streams in the "Results tab".
It might be possible to write an SSMS plug-in to capture info messages whenever someone runs a query and then pipe them back into a database table but that would have to be running on everyone's client machine identically to work properly.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato