Edit

Share via

sp_post_msx_operation (Transact-SQL)

  • Article

Applies to: SQL Server

Inserts operations (rows) into the sysdownloadlist system table for target servers to download and execute.

Transact-SQL syntax conventions

Syntax

sp_post_msx_operation
    [ @operation = ] 'operation'
    [ , [ @object_type = ] 'object_type' ]
    [ , [ @job_id = ] 'job_id' ]
    [ , [ @specific_target_server = ] N'specific_target_server' ]
    [ , [ @value = ] value ]
    [ , [ @schedule_uid = ] 'schedule_uid' ]
[ ; ]

Arguments

[ @operation = ] 'operation'

The type of operation for the posted operation. @operation is varchar(64), with no default. Valid operations depend upon @object_type.

Object type Operation
JOB INSERT
UPDATE
DELETE
START
STOP
SERVER RE-ENLIST
DEFECT
SYNC-TIME
SET-POLL
SCHEDULE INSERT
UPDATE
DELETE

[ @object_type = ] 'object_type'

The type of object for which to post an operation. @object_type is varchar(64), with a default of JOB. Valid types are JOB, SERVER, and SCHEDULE.

[ @job_id = ] 'job_id'

The job identification number of the job to which the operation applies. @job_id is uniqueidentifier, with a default of NULL. 0x00 indicates all jobs. If @object_type is SERVER, then @job_id isn't required.

[ @specific_target_server = ] N'specific_target_server'

The name of the target server for which the specified operation applies. @specific_target_server is sysname, with a default of NULL. If @job_id is specified, but @specific_target_server isn't specified, the operations are posted for all job servers of the job.

[ @value = ] value

The polling interval, in seconds. @value is int, with a default of NULL. Specify this parameter only if @operation is SET-POLL.

[ @schedule_uid = ] 'schedule_uid'

The unique identifier for the schedule to which the operation applies. @schedule_uid is uniqueidentifier, with a default of NULL.

Return code values

0 (success) or 1 (failure).

Result set

None.

Remarks

sp_post_msx_operation must be run from the msdb database.

sp_post_msx_operation can always be called safely because it first determines if the current server is a multiserver Microsoft SQL Server Agent and, if so, whether @object_type is a multiserver job.

After an operation is posted, it appears in the sysdownloadlist table. After a job is created and posted, subsequent changes to that job must also be communicated to the target servers (TSX). This step is also accomplished using the download list.

We highly recommend that you manage the download list in SQL Server Management Studio. For more information, see View or Modify Jobs.

Permissions

To run this stored procedure, users must be granted the sysadmin fixed server role.

Related content