I have the following query which needs to run on a schedule and email a list of all disabled jobs for the SQL Server instance (I've changed the server and email names for privacy):
@recipients = Nemail@example.com'
,@body = N'The attachment shows the names of all SQL jobs that are currently disabled on SERVER1'
,@subject = N'Disabled Jobs on SERVER1'
,@query = N'SELECT [NAME] FROM [msdb].[dbo].[sysjobs]'
,@attach_query_result_as_file = 1
This query works fine when running from a query window. however, when I try to run it from a SQL Agent job, I get the following error on the step output (username changed for privacy):
Executed as user: domain\user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
If I try other queries in the @query parameter, everything works fine when running from the job. As soon as I try to query [msdb].[dbo].[sysjobs] though, the job fails. I ran a profiler trace, and I found a more detailed error
message that occurs during the execution of the job, unfortunately it involves an undocument SP:
exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status
View Complete Post