Bewerken

Share via

Start a Job

  • Artikel

Applies to: SQL Server Azure SQL Managed Instance

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

This article describes how to start running a Microsoft SQL Server Agent job in SQL Server by using SQL Server Management Studio, Transact-SQL or SQL Server Management Objects.

A job is a specified series of actions that SQL Server Agent performs. SQL Server Agent jobs can run on one local server or on multiple remote servers.

Before You Begin

Security

For detailed information, see Implement SQL Server Agent Security.

Use SQL Server Management Studio

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Expand SQL Server Agent, and expand Jobs. Depending on how you want the job to start, do one of the following:

    • If you're working on a single server, or working on a target server, or running a local server job on a master server, right-click the job you want to start, and then select Start Job.

    • If you want to start multiple jobs, right-click Job Activity Monitor, and then select View Job Activity. In the Job Activity Monitor you can select multiple jobs, right-click your selection, and select Start Jobs.

    • If you're working on a master server and want all targeted servers to run the job simultaneously, right-click the job you want to start, select Start Job, and then select Start on all targeted servers.

    • If you're working on a master server and want to specify target servers for the job, right-click the job you want to start, select Start Job, and then select Start on specific target servers. In the Post Download Instructions dialog box, select the These target servers check box, and then select each target server on which this job should run.

Use Transact-SQL

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    -- starts a job named Weekly Sales Data Backup.
USE msdb ;
GO

EXEC dbo.sp_start_job N'Weekly Sales Data Backup' ;
GO

For more information, see sp_start_job (Transact-SQL).

Use programming languages

Call the Start method of the Job class by using a programming language that you choose, such as Visual Basic, Visual C#, or SQL PowerShell.

SQL PowerShell

Here's a PowerShell script that can be used in SQL Server Agent with parameters. This script demonstrates how to start a SQL Server Agent job using parameters passed into the script.

# Parameters
param(
    [string]$ServerInstance,
    [string]$JobName
)

# Load the SMO assembly
Add-Type -AssemblyName "Microsoft.SqlServer.SMO"

# Create a server object
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance

# Get the job you want to start
$job = $server.JobServer.Jobs[$JobName]

# Start the job
if ($job) {
    $job.Start()
    Write-Output "The job '$JobName' on server '$ServerInstance' has been started successfully."
} else {
    Write-Output "The job '$JobName' was not found on server '$ServerInstance'."
}

How to use the script in SQL Server Agent.

  1. Open SQL Server Management Studio (SSMS).

  2. Connect to the appropriate SQL Server instance.

  3. Expand the SQL Server Agent node.

  4. Right-click on Jobs and select New Job.

  5. In the New Job dialog box, enter the job name and other required details.

  6. Go to the Steps page and select New to create a new job step.

  7. In the New Job Step dialog box:

    1. Set the Type to PowerShell.

    2. In the Command field, enter the PowerShell script along with the parameters, for example:

      .\YourScript.ps1 -ServerInstance "YourServerInstance" -JobName "YourJobName"

  8. Set any other job properties as required (Schedules, Alerts, Notifications, etc.).

  9. Select OK to save the job.

Explanation of the script

  • Parameters: The script accepts two parameters, $ServerInstance and $JobName, which are the SQL Server instance and the job name respectively.
  • Load SMO: The Add-Type cmdlet is used to load the SQL Server Management Objects (SMO) assembly.
  • Server Object: A new server object is created using the $ServerInstance parameter.
  • Get Job: The script retrieves the specified job using the $JobName parameter.
  • Start Job: If the job is found, it's started using the Start method. The script outputs a success message. If the job isn't found, an error message is displayed.

For more information, see SQL Server Management Objects (SMO).

Related content