In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output?
Background: Just executing a statement like
UPDATE product SET ... will leave no trace in the job history (no matter whether “Include step output in history” is checked or not). Running the same statement in Query Analyzer shows
“(X row(s) affected)”.
By creating a variable and assigning it the @@ROWCOUNT contents after the update I can build and PRINT a message that will include the number of affected rows. This will be included in the job history.
This works fine, but I’d like to know the definition of output (that is written to history) from a Transact-SQL Script job Step. What is included except PRINTed strings?
One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window (formerly known as the Query Analyzer) of SQL Server Management Studio – but that is not the case.
For T-SQL Job Steps, the “output” refers to “messages” — notices sent via
RAISERROR. Result sets are also included as “output”, but only if there are no
RAISERROR messages, else it is only the
RAISERROR messages that are included.
Try this test:
Job Step 1
PRINT ' ** Line 1 ** '; SELECT ' ** Line 2 ** ' AS [Line Two]; RAISERROR(' ** Line 3 ** ', 10, 1);
Output in job history:
Executed as user: NT SERVICESQLSERVERAGENT. ** Line 1 ** [SQLSTATE 01000] (Message 0) ** Line 3 ** [SQLSTATE 01000] (Message 50000). The step succeeded.
Job Step 2
SELECT ' ** Line B1 ** ' AS [Line B-One]; --PRINT ' ** Line B2 ** '; -- uncomment and output will show this and not "Line B1"
Output in job history:
Executed as user: NT SERVICESQLSERVERAGENT. Line B-One
** Line B1 **
(1 rows(s) affected). The step succeeded.
Job Step 3
PRINT ' ** Start ** '; RAISERROR(' ** Test Exception ** ', 16, 1); PRINT ' ** End ** ';
Output in job history:
Executed as user: NT SERVICESQLSERVERAGENT. ** Start ** [SQLSTATE 01000] (Message 0) ** Test Exception ** [SQLSTATE 42000] (Error 50000) ** End ** [SQLSTATE 01000] (Message 0). The step failed.
Regarding the following statement in the Question:
One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window [in SSMS] [for example: “(X row(s) affected)”]
One certainly might think that, but what is shown in the Messages tab of SSMS is not necessarily direct output from SQL Server. The “X rows(s) affected” is being generated by SSMS based on info it received from SQL Server that was not direct output but additional info that comes back in the Tabular Data Stream (TDS). The same applies to batch iteration using “GO x” where the
x is an integer telling SSMS how many times to submit that particular batch (the one ending with the “GO” batch separator). The output in the “Messages” tab would show
Beginning execution loop and at the end
Batch execution completed 4 times., but those are messages from SSMS and not from SQL Server.
If you direct output for the step into a “table”, like this:
You can see all output from the last run of the job, including
RAISERROR, and results from queries, by looking in the msdb database at the
SELECT JobName = sj.name , StepName = sjs.step_name , DateModified = sjsl.date_modified , LogText = sjsl.log FROM dbo.sysjobs sj INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id INNER JOIN dbo.sysjobstepslogs sjsl ON sjs.step_uid = sjsl.step_uid;
One gotcha, you’ll need to copy-and-paste the contents of the
LogText column into notepad (or editor of your choice) to see multiple lines of output.
With a job defined as:
USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestOutput', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'[login_name]', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Step1] Script Date: 4/14/2016 2:41:19 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'SELECT d.name FROM sys.databases d WHERE d.database_id > 4 ORDER BY d.name; PRINT ''test'';', @database_name=N'master', @flags=8 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
Output from the query looks like:
and the copy-and-paste text looks like:
Job 'TestOutput' : Step 1, 'Step1' : Began Executing 2016-04-14 14:38:44 name -------------------------------------------------------------------------------------------------------------------------------- Test (1 rows(s) affected) test [SQLSTATE 01000]