Compartir a través de


Targeting SQL Server Agent Jobs

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

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"

Vea también

Referencia

ApplyToTargetServer Method
ApplyToTargetServerGroup Method
EnumTargetServers Method
Job Object
RemoveFromTargetServer Method

Ayuda e información

Obtener ayuda sobre SQL Server 2005