Allows the SQL text of
a job to be manipulated. When @option=’list’,
the SQL text is listed.
By default, the @name argument
is the name or ID of a scheduled job.
To specify the name or ID of a job, the @name argument
is prefixed with jname.
sp_sjobcmd @name=’…’, @option=’…’, @text=’...’
The name or ID of a scheduled job or job. If it is prefixed with jname, the name or ID of a job.
An option to select add, list, or drop to the SQL text for a job.
drop – deletes all SQL text for the specified job.
list – displays the job command text stored in the database.
add – appends to any existing SQL text for the specified job, allowing large SQL batches to be stored in several chunks. When JS Agent runs the job, the SQL text for the job is concatenated.
The SQL text to store.
Returns 0 or an error code.
The following example lists the SQL text for the job that is referenced by the scheduled job called “svr1_check_stats”:
sp_sjobcmd ’sjname=svr1_check_stats’, ’list’
The following example deletes the existing SQL text for the “load_sales_data” job and then stores new SQL text for the job:
sp_sjobcmd ’jname=load_sales_data’, ’drop’ sp_sjobcmd ’jname=load_sales_data’, ’add’, @text=’truncate table sales_report_data’ go ’
Job Scheduler does not implicitly add a new line at the end of the job text. So, when you create a job with sp_sjobcmd, you must explicitly add a new line after you execute a call:
sp_sjobcmd ’jname=load_sales_data’, ’drop’ sp_sjobcmd ’jname=load_sales_data’, ’add’, @text=’truncate table sales_report_data’ go ’
