SQL Server Agent musing - T-SQL code in the job steps

It turns out that the SQL Server Agent does not take part in sysdepends. This allows the SQL Server to warn if an object is misspelled: i.e.

--create a procedure that calls an object that doesn't exist
CREATE PROCEDURE tmpBlah AS
--execute a procedure that doesn't exist
EXEC dbo.blah2
GO
--clean up the procedure
DROP PROCEDURE tmpBlah

Will return:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.blah2'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

Creating a SQL Server Agent job step and setting the command text to:

EXEC dbo.blah2

No warning. When the job runs, it will fail on that step withOne has to wait for the job history in order to see the error:

Could not find stored procedure 'dbo.blah2'

This reinforces a standard I've used of storing most T-SQL code that would otherwise be scheduled in a stored procedure within the target database.

In a recent project I'm working, a large amount of the data caching and aggregation code was stored in the SQL Server Agent job steps. I've established a development standard of storing most code inside the target database, and limited Agent job steps to:

• SQL Server Integration Services Package


• Stored procedure – ideally within the target database


• Limited small T-SQL commands such as truncating tables, creating cached copies (as soon as the commands are more than a few lines, or are very domain-specific, they will be moved to a stored procedure)


• Maintenance tasks (such as DBCC DBREINDEX(@TableName,' ',90)