What is considered for output from an sql server job step?
For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT
and RAISERROR
. Result sets are also included as "output", but only if there are no PRINT
/ RAISERROR
messages, else it is only the PRINT
/ RAISERROR
messages that are included.
Try this test:
Job Step 1
Definition:
PRINT ' ** Line 1 ** ';
SELECT ' ** Line 2 ** ' AS [Line Two];
RAISERROR(' ** Line 3 ** ', 10, 1);
Output in job history:
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. ** Line 1 ** [SQLSTATE 01000] (Message 0) ** Line 3 ** [SQLSTATE 01000] (Message 50000). The step succeeded.
Job Step 2
Definition:
SELECT ' ** Line B1 ** ' AS [Line B-One];
--PRINT ' ** Line B2 ** '; -- uncomment and output will show this and not "Line B1"
Output in job history:
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Line B-One
---------------
** Line B1 **
(1 rows(s) affected). The step succeeded.
Job Step 3
Definition:
PRINT ' ** Start ** ';
RAISERROR(' ** Test Exception ** ', 16, 1);
PRINT ' ** End ** ';
Output in job history:
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. ** 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 PRINT
, RAISERROR
, and results from queries, by looking in the msdb database at the dbo.sysjobstepslogs
table:
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]