SQL Server – Output Considerations for Job Step

jobssql serversql-server-2012sql-server-agent

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.

Best Answer

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.

Related Question