sp_change_subscription_properties
Updates the security information in the MSsubscription_properties table. This stored procedure is executed at the Publisher on the publication database.
Syntax
sp_change_subscription_properties [ @publisher = ] 'publisher'
, [ @publisher_db = ] 'publisher_db'
, [ @publication = ] 'publication'
, [ @property = ] 'property'
, [ @value = ] 'value'
[ , [ @publication_type = ] publication_type ]
Arguments
[@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, and can be one of these values.
| Value | Description |
|---|---|
| publisher_login | Publisher login. |
| publisher_password | Publisher password. |
| publisher_security_mode | Security mode implemented at the Publisher. Can be:
0 = SQL Server Authentication |
| distributor_login | Distributor login. |
| distributor_password | Distributor password. |
| distributor_security_mode | Security mode implemented at the Distributor. Can be:
0 = SQL Server Authentication |
| encrypted_distributor_password | For internal use only. |
| ftp_address | For backward compatibility only. |
| ftp_port | For backward compatibility only. |
| ftp_login | For backward compatibility only. |
| ftp_password | For backward compatibility only. |
| alt_snapshot_folder | Specifies the location of the alternate folder for the snapshot. alt_snapshot_folder is nvarchar(255). If set to NULL, the snapshot files will be picked up from the default location specified by the Publisher. |
| working_directory | Name of the working directory used to temporarily store data and schema files for the publication when FTP is used to transfer snapshot files. working_directory is nvarchar(255). |
| use_ftp | Specifies the use of FTP instead of the regular protocol to retrieve snapshots. If 1, FTP is used. use_ftp is a bit field. |
| ofload_agent | Specifies if the agent can be activated remotely. If 0, the agent cannot be activated remotely. offload_agent is a bit field. |
| offload_server | Specifies the network name of the server used for remote activation. |
| dts_package_name | Specifies the name of the DTS package. This value can be specified only if the publication is transactional or snapshot. |
| dts_package_password | Specifies the password on the package, if there is one. A value of NULL means that the package has no password. This value can be specified only if the publication is transactional or snapshot. |
| dts_package_location | Location where the DTS package is stored. This value can be specified only if the publication is transactional or snapshot. |
| dynamic_snapshot_location | Specifies the path to the folder where the snapshot files are saved. This value can be specified only if the publication is a merge publication. |
[@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, with a default of NULL. If NULL, specifies an unknown publication type and the stored procecure looks at all transaction tables to find out the publication type. Because the stored proc must look through multiple tables, this option will be slower than when the exact publication type of 0, 1, or 2 is specified. If 0, publication is a transaction type. If 1, publication is a snapshot type. If 2, publication is a merge type.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_change_subscription_properties is used in all types of replication.
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_change_subscription_properties.