| title | sp_addpullsubscription_agent (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 08/08/2019 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | replication | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | b9c2eaed-6d2d-4b78-ae9b-73633133180b | ||
| author | stevestein | ||
| ms.author | sstein |
[!INCLUDEappliesto-ss-asdbmi-xxxx-xxx-md]
Adds a new scheduled agent job used to synchronize a pull subscription to a transactional publication. This stored procedure is executed at the Subscriber on the subscription database.
Transact-SQL Syntax Conventions
sp_addpullsubscription_agent [ @publisher = ] 'publisher'
[ , [ @publisher_db = ] 'publisher_db' ] , [ @publication = ] 'publication'
[ , [ @subscriber = ] 'subscriber' ]
[ , [ @subscriber_db = ] 'subscriber_db' ]
[ , [ @subscriber_security_mode = ] subscriber_security_mode ]
[ , [ @subscriber_login = ] 'subscriber_login' ]
[ , [ @subscriber_password = ] 'subscriber_password' ]
[ , [ @distributor = ] 'distributor' ]
[ , [ @distribution_db = ] 'distribution_db' ]
[ , [ @distributor_security_mode = ] distributor_security_mode ]
[ , [ @distributor_login = ] 'distributor_login' ]
[ , [ @distributor_password = ] 'distributor_password' ]
[ , [ @optional_command_line = ] 'optional_command_line' ]
[ , [ @frequency_type = ] frequency_type ]
[ , [ @frequency_interval = ] frequency_interval ]
[ , [ @frequency_relative_interval = ] frequency_relative_interval ]
[ , [ @frequency_recurrence_factor = ] frequency_recurrence_factor ]
[ , [ @frequency_subday = ] frequency_subday ]
[ , [ @frequency_subday_interval = ] frequency_subday_interval ]
[ , [ @active_start_time_of_day = ] active_start_time_of_day ]
[ , [ @active_end_time_of_day = ] active_end_time_of_day ]
[ , [ @active_start_date = ] active_start_date ]
[ , [ @active_end_date = ] active_end_date ]
[ , [ @distribution_jobid = ] distribution_jobid OUTPUT ]
[ , [ @encrypted_distributor_password = ] encrypted_distributor_password ]
[ , [ @enabled_for_syncmgr = ] 'enabled_for_syncmgr' ]
[ , [ @ftp_address = ] 'ftp_address' ]
[ , [ @ftp_port = ] ftp_port ]
[ , [ @ftp_login = ] 'ftp_login' ]
[ , [ @ftp_password = ] 'ftp_password' ]
[ , [ @alt_snapshot_folder = ] 'alternate_snapshot_folder' ]
[ , [ @working_directory = ] 'working_directory' ]
[ , [ @use_ftp = ] 'use_ftp' ]
[ , [ @publication_type = ] publication_type ]
[ , [ @dts_package_name = ] 'dts_package_name' ]
[ , [ @dts_package_password = ] 'dts_package_password' ]
[ , [ @dts_package_location = ] 'dts_package_location' ]
[ , [ @reserved = ] 'reserved' ]
[ , [ @offloadagent = ] 'remote_agent_activation' ]
[ , [ @offloadserver = ] 'remote_agent_server_name']
[ , [ @job_name = ] 'job_name' ]
[ , [ @job_login = ] 'job_login' ]
[ , [ @job_password = ] 'job_password' ]
[ @publisher = ] 'publisher'
Is the name of the Publisher. publisher is sysname, with no default.
[ @publisher_db = ] 'publisher_db'_
Is the name of the Publisher database. publisher_db is sysname, with a default value of NULL. publisher_db is ignored by Oracle Publishers.
[ @publication = ] 'publication'
Is the name of the publication. publication is sysname, with no default.
[ @subscriber = ] 'subscriber'
Is the name of the Subscriber instance or the name of the AG listener if the subscriber database is in an availability group. subscriber is sysname, with a default of NULL.
Note
This parameter has been deprecated and is maintained for backward compatibility of scripts.
[ @subscriber_db = ] 'subscriber_db'
Is the name of the subscription database. subscriber_db is sysname, with a default of NULL.
Note
This parameter has been deprecated and is maintained for backward compatibility of scripts.
[ @subscriber_security_mode = ] subscriber_security_mode
Is the security mode to use when connecting to a Subscriber when synchronizing. subscriber_security_mode is int, with a default of NULL. 0 specifies [!INCLUDEssNoVersion] Authentication. 1 specifies Windows Authentication.
Note
This parameter has been deprecated and is maintained for backward compatibility of scripts. The Distribution Agent always connects to the local Subscriber using Windows Authentication. If a value other than NULL or 1 is specified for this parameter, a warning message is returned.
[ @subscriber_login = ] 'subscriber_login'
Is the Subscriber login to use when connecting to a Subscriber when synchronizing.subscriber_login is sysname, with a default of NULL.
Note
This parameter has been deprecated and is maintained for backward compatibility of scripts. If a value is specified for this parameter, a warning message is returned, but the value is ignored.
[ @subscriber_password = ] 'subscriber_password'
Is the Subscriber password. subscriber_password is required if subscriber_security_mode is set to 0. subscriber_password is sysname, with a default of NULL. If a subscriber password is used, it is automatically encrypted.
Note
This parameter has been deprecated and is maintained for backward compatibility of scripts. If a value is specified for this parameter, a warning message is returned, but the value is ignored.
[ @distributor = ] 'distributor'
Is the name of the Distributor. distributor is sysname, with a default of the value specified by publisher.
[ @distribution_db = ] 'distribution_db'
Is the name of the distribution database. distribution_db is sysname, with a default value of NULL.
[ @distributor_security_mode = ] distributor_security_mode
Is the security mode to use when connecting to a Distributor when synchronizing. distributor_security_mode is int, with a default of 1. 0 specifies [!INCLUDEssNoVersion] Authentication. 1 specifies Windows Authentication.
Important
[!INCLUDEssNoteWinAuthentication]
[ @distributor_login = ] 'distributor_login'
Is the Distributor login to use when connecting to a Distributor when synchronizing. distributor_login is required if distributor_security_mode is set to 0. distributor_login is sysname, with a default of NULL.
[ @distributor_password = ] 'distributor_password'
Is the Distributor password. distributor_password is required if distributor_security_mode is set to 0. distributor_password is sysname, with a default of NULL.
Important
Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.
[ @optional_command_line = ] 'optional_command_line'
Is an optional command prompt supplied to the Distribution Agent. For example, -DefinitionFile C:\Distdef.txt or -CommitBatchSize 10. optional_command_line is nvarchar(4000), with a default of empty string.
[ @frequency_type = ] frequency_type
Is the frequency with which to schedule the Distribution Agent. frequency_type is int, and can be one of the following values.
| Value | Description |
|---|---|
| 1 | One time |
| 2 (default) | On demand |
| 4 | Daily |
| 8 | Weekly |
| 16 | Monthly |
| 32 | Monthly relative |
| 64 | Autostart |
| 128 | Recurring |
Note
Specifying a value of 64 causes the Distribution Agent to run in continuous mode. This corresponds to setting the -Continuous parameter for the agent. For more information, see Replication Distribution Agent.
[ @frequency_interval = ] frequency_interval
Is the value to apply to the frequency set by frequency_type. frequency_interval is int, with a default of 1.
[ @frequency_relative_interval = ] frequency_relative_interval
Is the date of the Distribution Agent. This parameter is used when frequency_type is set to 32 (monthly relative). frequency_relative_interval is int, and can be one of the following values.
| Value | Description |
|---|---|
| 1 (default) | First |
| 2 | Second |
| 4 | Third |
| 8 | Fourth |
| 16 | Last |
[ @frequency_recurrence_factor = ] frequency_recurrence_factor
Is the recurrence factor used by frequency_type. frequency_recurrence_factor is int, with a default of 1.
[ @frequency_subday = ] frequency_subday
Is how often to reschedule during the defined period. frequency_subday is int, and can be one of the following values.
| Value | Description |
|---|---|
| 1 (default) | Once |
| 2 | Second |
| 4 | Minute |
| 8 | Hour |
[ @frequency_subday_interval = ] frequency_subday_interval
Is the interval for frequency_subday. frequency_subday_interval is int, with a default of 1.
[ @active_start_time_of_day = ] active_start_time_of_day
Is the time of day when the Distribution Agent is first scheduled, formatted as HHMMSS. active_start_time_of_day is int, with a default of 0.
[ @active_end_time_of_day = ] active_end_time_of_day
Is the time of day when the Distribution Agent stops being scheduled, formatted as HHMMSS. active_end_time_of_day is int, with a default of 0.
[ @active_start_date = ] active_start_date
Is the date when the Distribution Agent is first scheduled, formatted as YYYYMMDD. active_start_date is int, with a default of 0.
[ @active_end_date = ] active_end_date
Is the date when the Distribution Agent stops being scheduled, formatted as YYYYMMDD. active_end_date is int, with a default of 0.
[ @distribution_jobid = ] _distribution_jobidOUTPUT
Is the ID of the Distribution Agent for this job. distribution_jobid is binary(16), with a default of NULL, and it is an OUTPUT parameter.
[ @encrypted_distributor_password = ] encrypted_distributor_password
Setting encrypted_distributor_password is no longer supported. Attempting to set this bit parameter to 1 will result in an error.
[ @enabled_for_syncmgr = ] 'enabled_for_syncmgr'
Is whether the subscription can be synchronized through [!INCLUDEmsCoName] Synchronization Manager. enabled_for_syncmgr is nvarchar(5), with a default of FALSE. If false, the subscription is not registered with Synchronization Manager. If true, the subscription is registered with Synchronization Manager and can be synchronized without starting [!INCLUDEssManStudioFull].
[ @ftp_address = ] 'ftp_address'
For backward compatibility only.
[ @ftp_port = ] ftp_port
For backward compatibility only.
[ @ftp_login = ] 'ftp_login'
For backward compatibility only.
[ @ftp_password = ] 'ftp_password'
For backward compatibility only.
[ @alt_snapshot_folder = ] 'alternate_snapshot_folder'_
Specifies the location of the alternate folder for the snapshot. alternate_snapshot_folder is nvarchar(255), with a default of NULL.
[ @working_directory = ] 'working_director'
Is the name of the working directory used to store data and schema files for the publication. working_directory is nvarchar(255), with a default of NULL. The name should be specified in UNC format.
[ @use_ftp = ] 'use_ftp'
Specifies the use of FTP instead of the regular protocol to retrieve snapshots. use_ftp is nvarchar(5), with a default of FALSE.
[ @publication_type = ] publication_type
Specifies the replication type of the publication. publication_type is a tinyint with a default of 0. If 0, publication is a transaction type. If 1, publication is a snapshot type. If 2, publication is a merge type.
[ @dts_package_name = ] 'dts_package_name'
Specifies the name of the DTS package. dts_package_name is a sysname with a default of NULL. For example, to specify a package of DTSPub_Package, the parameter would be @dts_package_name = N'DTSPub_Package'.
[ @dts_package_password = ] 'dts_package_password'
Specifies the password on the package, if there is one. dts_package_password is sysname with a default of NULL, which means a password is not on the package.
Note
You must specify a password if dts_package_name is specified.
[ @dts_package_location = ] 'dts_package_location'
Specifies the package location. dts_package_location is a nvarchar(12), with a default of subscriber. The location of the package can be distributor or subscriber.
[ @reserved = ] 'reserved'
[!INCLUDEssInternalOnly]
[ @offloadagent = ] 'remote_agent_activation'
Note
Remote agent activation has been deprecated and is no longer supported. This parameter is supported only to maintain backward compatibility of scripts. Setting remote_agent_activation to a value other than false will generate an error.
[ @offloadserver = ] 'remote_agent_server_name'
Note
Remote agent activation has been deprecated and is no longer supported. This parameter is supported only to maintain backward compatibility of scripts. Setting remote_agent_server_name to any non-NULL value will generate an error.
[ @job_name = ] 'job_name'
Is the name of an existing agent job. job_name is sysname, with a default value of NULL. This parameter is only specified when the subscription will be synchronized using an existing job instead of a newly created job (the default). If you are not a member of the sysadmin fixed server role, you must specify job_login and job_password when you specify job_name.
[ @job_login = ] 'job_login'
Is the login for the Windows account under which the agent runs. job_login is nvarchar(257), with no default. This Windows account is always used for agent connections to the Subscriber.
[ @job_password = ] 'job_password'
Is the password for the Windows account under which the agent runs. job_password is sysname, with no default.
Important
When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.
0 (success) or 1 (failure)
sp_addpullsubscription_agent is used in snapshot replication and transactional replication.
[!code-sqlHowTo#sp_addtranpullsubscriptionagent]
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addpullsubscription_agent.
Create a Pull Subscription
Subscribe to Publications
sp_addpullsubscription (Transact-SQL)
sp_change_subscription_properties (Transact-SQL)
sp_droppullsubscription (Transact-SQL)
sp_helppullsubscription (Transact-SQL)
sp_helpsubscription_properties (Transact-SQL)