Retrieving messages printed during sp_executesql

Question

I am running a command through sp_executesql in a SQL Server Agent. How do I capture messages from this command so I can log them in a table?

In particular I am attempting to truncate the database log and would like to be able to see if this succeeded. I am adding the code snippet in the agent SP:

IF (@truncate = 1)
BEGIN
  SET @msg = 'Truncating log ''' + @description + ''' for the ''' + @db + ''' database'
  EXEC _LogInsert 'Information', '_Loop', 'Status', @msg

  --PRINT 'Attempting to truncate the log file'
  SET @sql = N'USE [' + @db + ']' +

'DECLARE @LogFile varchar(128)' +

'SELECT TOP 1 @LogFile = [Name] ' +
'FROM sys.database_files ' +
'WHERE ([Type_Desc] = ''LOG'')' +

'DBCC SHRINKFILE(@LogFile, EMPTYFILE)' +
'DBCC SHRINKFILE(@LogFile, 10) WITH NO_INFOMSGS'
  --PRINT 'truncation attempt finished'
EXEC sp_executesql @sql

  -- Capture messages from SP
  -- SET @msg = 'Truncating log msg ''' + @resmsg + ''' for the ''' + @db + ''' database'
  -- EXEC _LogInsert 'Information', '_Loop', 'Status', @msg


END

You cannot easily capture all messages. You can however catch most exceptions and then log the messages associated with them. — RBarryYoung, Dec 02 '16 at 02:55
That's a shame because we are using `sp_refreshsqlmodule` which does not fail for procs with invalid references, but does print _depends on the missing object...._ If we could only capture this message we could identify invalid procs — Nick.Mc, Nov 24 '21 at 09:12

score 0 · Accepted Answer · answered Dec 02 '16 at 07:55

If you run this as a job you can take data you need (at least DBCC output, or PRINT) from dbo.sysjobhistory:

SELECT  jh.[run_date],
        jh.[step_name],
        jh.[message]
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
    ON j.[name] = 'job_name' and j.job_id = jh.job_id

Also, pay attention to WITH NO_INFOMSGS- this suppresses all informational messages.

Retrieving messages printed during sp_executesql

1 Answers1