sp_change_subscription_properties (Transact-SQL)

 

Applies To: SQL Server

Updates information for pull subscriptions. This stored procedure is executed at the Subscriber on the subscription database.

Topic link icon Transact-SQL Syntax Conventions

  
sp_change_subscription_properties [ @publisher = ] 'publisher'  
        , [ @publisher_db = ] 'publisher_db'  
        , [ @publication = ] 'publication'  
        , [ @property = ] 'property'  
        , [ @value = ] 'value'  
    [ , [ @publication_type = ] publication_type ]  

[ @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 no default.

[ @publication=] 'publication'
Is the name of the publication. publication is sysname, with no default.

[ @property=] 'property'
Is the property to be changed. property is sysname.

[ @value=] 'value'
Is the new value of the property. value is nvarchar(1000), with no default.

[ @publication_type = ] publication_type
Specifies the replication type of the publication. publication_type is int, and can be one of these values.

ValuePublication type
0Transactional
1Snapshot
2Merge
NULL (default)Replication determines the publication type. Because the stored procedure must look through multiple tables, this option is slower than when the exact publication type is provided.

This table describes the properties of articles and the values for those properties.

PropertyValueDescription
alt_snapshot_folderSpecifies the location of the alternate folder for the snapshot. If set to NULL, the snapshot files are picked up from the default location specified by the Publisher.
distrib_job_loginLogin for the Microsoft Windows account under which the agent runs.
distrib_job_passwordPassword for the Windows account under which the agent runs.
distributor_loginDistributor login.
distributor_passwordDistributor password.
distributor_security_mode1Use Windows Authentication when connecting to the Distributor.
0Use SQL Server Authentication when connecting to the Distributor.
dts_package_nameSpecifies the name of the SQL Server 2000 Data Transformation Services (DTS) package. This value can be specified only if the publication is transactional or snapshot.
dts_package_passwordSpecifies the password on the package. dts_package_password is sysname with a default of NULL, which specifies that the password property is to be left unchanged.

Note: A DTS package must have a password.

This value can be specified only if the publication is transactional or snapshot.
dts_package_locationLocation where the DTS package is stored. This value can be specified only if the publication is transactional or snapshot.
dynamic_snapshot_locationSpecifies the path to the folder where the snapshot files are saved. This value can be specified only if the publication is a merge publication.
ftp_addressFor backward compatibility only.
ftp_loginFor backward compatibility only.
ftp_passwordFor backward compatibility only.
ftp_portFor backward compatibility only.
hostnameHost name used when connecting to the Publisher.
internet_loginLogin that the Merge Agent uses when connecting to the Web server that is hosting Web synchronization using Basic Authentication.
internet_passwordPassword that the Merge Agent uses when connecting to the Web server that is hosting Web synchronization using Basic Authentication.
internet_security_mode1Use Windows Integrated Authentication for Web synchronization. We recommend using Basic Authentication with Web synchronization. For more information, see Configure Web Synchronization.
0Use Basic Authentication for Web synchronization.

Note: Web synchronization requires an SSL connection to the Web server.
internet_timeoutLength of time, in seconds, before a Web synchronization request expires.
internet_urlURL that represents the location of the replication listener for Web synchronization.
merge_job_loginLogin for the Windows account under which the agent runs.
merge_job_passwordPassword for the Windows account under which the agent runs.
publisher_loginPublisher login. Changing publisher_login is only supported for subscriptions to merge publications.
publisher_passwordPublisher password. Changing publisher_password is only supported for subscriptions to merge publications.
publisher_security_mode1Use Windows Authentication when connecting to the Publisher. Changing publisher_security_mode is only supported for subscriptions to merge publications.
0Use SQL Server Authentication when connecting to the Publisher.
use_ftptrueUse FTP instead of the regular protocol to retrieve snapshots.
falseUse the regular protocol to retrieve snapshots.
use_web_synctrueEnable Web synchronization.
falseDisable Web synchronization.
working_directoryName of the working directory used to temporarily store data and schema files for the publication when File Transfer Protocol (FTP) is used to transfer snapshot files.

0 (success) or 1 (failure)

sp_change_subscription_properties is used in all types of replication.

sp_change_subscription_properties is used for pull subscriptions.

For Oracle Publishers, the value of publisher_db is ignored since Oracle only allows one database per instance of the server.

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_change_subscription_properties.

View and Modify Pull Subscription Properties
sp_addmergepullsubscription (Transact-SQL)
sp_addmergepullsubscription_agent (Transact-SQL)
sp_addpullsubscription (Transact-SQL)
sp_addpullsubscription_agent (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show: