--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)
No comments:
Post a Comment