Targeting SQL Server Agent Jobs
SQL-DMO will be removed in the next version of Microsoft SQL Server. Avoid using SQL-DMO feature in new development work, and plan to modify applications that use SQL-DMO.
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 assignments. When using the ApplyToTargetServer or ApplyToTargetServerGroup methods, SQL Distributed Management Objects (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 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 Target Servers
This example illustrates assigning execution targets for a SQL Server Agent job. The execution targets are several target servers 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"