Transact-SQL Reference

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
1 = Windows Authentication

distributor_login Distributor login.
distributor_password Distributor password.
distributor_security_mode Security mode implemented at the Distributor. Can be:

0 = SQL Server Authentication
1 = Windows 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.

See Also

System Stored Procedures