What is considered for output from an sql server job step?

Question

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.

Answer 1

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.

Answer 2

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]