Targeting SQL Server Agent Jobs
These examples illustrate assigning SQL Server Agent job execution targets. A job can be run by SQL Server Agent when it contains at least one step and an execution target.
In these examples, the EnumTargetServers and RemoveFromTargetServer methods are used to remove existing execution target assignment(s). When using the ApplyToTargetServer or ApplyToTargetServerGroup methods, SQL-DMO returns an error if an attempt is made to indicate an execution target redundantly. A SQL Server Agent job may be targeted to execute on either the local instance of Microsoft® SQL Server™ (the instance on which SQL Server Agent executes) or one or more target servers (TSXs) in a multiserver administration group. A job cannot have both the local instance and any other server as execution targets. By removing existing assignments, the examples ensure success of the execution target assignment made later in the example.
Examples
A. Targeting a Local Server
This example illustrates assigning an execution target for a SQL Server Agent job. The execution target is the local instance of SQL Server.
Dim oJob As SQLDMO.Job
' A QueryResults object will be used to test for current target
' server assignment.
Dim oQueryResults As SQLDMO.QueryResults
Dim iRow As Integer
' Get the job to target. Note: Create and connect of SQLServer object
' is not illustrated in this example.
Set oJob = oSQLServer.JobServer.Jobs ("Backup_Northwind_Filegroups")
' Enumerate existing target servers for the job.
Set oQueryResults = oJob.EnumTargetServers
For iRow = 1 To oQueryResults.Rows
' The target server name is the second column in the result set.
oJob.RemoveFromTargetServer _
oQueryResults.GetColumnString(iRow, 2)
Next iRow
' Target the local server, the server to which the SQLServer object is
' connected and from which the job has been retrieved.
oJob.ApplyToTargetServer "(Local)"
B. Targeting TSX Servers
This example illustrates assigning execution targets for a SQL Server Agent job. The execution targets are several TSXs in a multiserver administration group.
Dim oJob As SQLDMO.Job
' A QueryResults object will be used to test for current target
' server assignment.
Dim oQueryResults As SQLDMO.QueryResults
Dim iRow As Integer
' Get the job to target. Note: Create and connect of SQLServer object
' is not illustrated in this example.
Set oJob = oSQLServer.JobServer.Jobs ("Backup_Northwind_Filegroups")
' Enumerate existing target servers for the job.
Set oQueryResults = oJob.EnumTargetServers
For iRow = 1 To oQueryResults.Rows
' The target server name is the second column in the result set.
oJob.RemoveFromTargetServer _
oQueryResults.GetColumnString(iRow, 2)
Next iRow
' Target a server group and a single server. Note: creation of target
' servers and target server groups is not illustrated in this example.
oJob.ApplyToTargetServerGroup "London"
oJob.ApplyToTargetServer "SEATTLE2"