marcialwushu
commented
5 years ago Create a Transact-SQL Job Step
This topic describes how to create a Microsoft SQL Server Agent job step that executes Transact-SQL scripts in SQL Server 2017 by using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects.
These job step scripts may call stored procedures and extended stored procedures. A single Transact-SQL job step can contain multiple batches and embedded GO commands. For more information on creating a job, see Creating Jobs.
In This Topic
-
Before you begin:
- Security
-
To create a Transact-SQL job step, using:
-
SQL Server Management Studio
-
Transact-SQL
-
SQL Server Management Objects
-
Before You Begin
Security
For detailed information, see Implement SQL Server Agent Security.
Using SQL Server Management Studio
To create a Transact-SQL job step
-
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
-
Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.
-
In the Job Properties dialog, click the Steps page, and then click New.
-
In the New Job Step dialog, type a job Step name.
-
In the Type list, click Transact-SQL Script (TSQL).
-
In the Command box, type the Transact-SQL command batches, or click Open to select a Transact-SQL file to use as the command.
-
Click Parse to check your syntax.
-
The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.
-
Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file or table where SQL Server Agent can write the job step output. Only members of the sysadmin fixed server role can write job step output to an operating system file. All SQL Server Agent users can log output to a table.
-
If you are a member of the sysadmin fixed server role and you want to run this job step as a different SQL login, select the SQL login from the Run as user list.
Using Transact-SQL
To create a Transact-SQL job step
-
In Object Explorer, connect to an instance of Database Engine.
-
On the Standard bar, click New Query.
-
Copy and paste the following example into the query window and click Execute.
-- creates a job step that uses Transact-SQL
USE msdb;
GO
EXEC sp_add_jobstep
@job_name = N'Weekly Sales Data Backup',
@step_name = N'Set database to read only',
@subsystem = N'TSQL',
@command = N'ALTER DATABASE SALES SET READ_ONLY',
@retry_attempts = 5,
@retry_interval = 5 ;
GO Using SQL Server Management Objects
To create a Transact-SQL job step
Use the JobStep class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell.
I recently came across a way to script up all agent jobs in one go within SQL Server Management Studio (SSMS). Previously I used to right click on each job and select ‘Script Job as’ to script up each one individually, but this can be very labourious if there are a lot of jobs to do.
To script all jobs, just open the ‘Object Explorer Details’ from the View menu in SSMS, or press the F7 key. Click on any job in the Object Explorer window and a list of all the agent jobs appears in the ‘Object Explorer Details’ window.
Select all the jobs you want to script (press the Ctrl button while clicking to select individual jobs) and then right click and select the scripting option you want. This will then create all the selected jobs as a single query. You can script to a query window, file etc.
ORIGINAL