In this article, I present four options for using T-SQL to return the schedules that are attached to a SQL Server Agent job.
t-sql
Create a SQL Server Agent Schedule with T-SQL
When using T-SQL, you can use the sp_add_schedule stored procedure to add a SQL Server Agent schedule.
This procedure creates the schedule, but it doesn’t attach it to any jobs. To attach it to a job, you must use the sp_attach_schedule procedure.
You can also use sp_add_jobschedule to create the schedule and attach it all with the same procedure.
3 Ways to Return a List of SQL Server Agent Jobs (T-SQL)
Often the quickest way to get a list of SQL Server Agent jobs is to simply expand the SQL Server Agent node in the SSMS Object Explorer.
But that’s not the only way. And depending on your environment, it might not even be an option. If you don’t have SSMS or a database tool that allows you to see the SQL Server Agent jobs, you may need to use T-SQL instead.
In this article, I present three options for returning a list of SQL Server Agent jobs when using T-SQL.
4 Ways to Get SQL Server Job History
In this article, I present four options for returning SQL Server Agent job history data.
Fix “SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries'”
If you get error Msg 15281, Level 16 in SQL Server, chances are you’re trying to run an ad hoc distributed query, but you haven’t enabled ad hoc distributed queries.
This can be fixed easily by enabling ad hoc distributed queries.
Using sp_help_schedule in SQL Server
In SQL Server, the sp_help_schedule stored procedure returns information about schedules in the SQL Server Agent.
This article provides an overview of this stored procedure including examples.
Using sp_help_jobschedule in SQL Server
In SQL Server, the sp_help_jobschedule stored procedure returns information about the scheduling of jobs.
This article provides an overview of this stored procedure including examples.
Add a Job Step to an Existing SQL Server Agent Job (T-SQL)
When you create a SQL Server Agent job with T-SQL, you use the sp_add_jobstep stored procedure to add each job step.
If you ever need to add a new step to that job, you can use sp_add_jobstep once again to add the new job step.
But you may also need to modify the existing step, depending on how you’d like the job to progress through the steps.
Modify a SQL Server Agent Job (T-SQL)
In SQL Server, you can use the sp_update_job stored procedure to modify an existing SQL Server Agent job.
Update a Job Step for a SQL Server Agent Job (T-SQL)
In SQL Server, you can use the sp_update_jobstep stored procedure to modify an existing job step of a SQL Server Agent job.
When you create a SQL Server Agent job, you create one or more job steps for that job. The sp_update_jobstep procedure allows you to update a specific job step independent of the actual job or any other job steps.