sp_link_publication
Sets the configuration and security information used by synchronization triggers of all updatable subscriptions when connecting to the Publisher. This stored procedure is executed at the Subscriber on the subscription database.
Syntax
sp_link_publication [ @publisher = ] 'publisher'
, [ @publisher_db = ] 'publisher_db'
, [ @publication = ] 'publication'
, [ @security_mode = ] security_mode
[ , [ @login =] 'login' ]
[ , [ @password = ] 'password' ]
[, [ @distributor = ] 'distributor' ]
Arguments
[@publisher = ] 'publisher'
Is the name of the Publisher to link to. publisher is sysname, with no default.
[@publisher_db = ] 'publisher_db'
Is the name of the Publisher database to link to. publisher_db is sysname, with no default.
[@publication = ] 'publication'
Is the name of the publication to link to. publication is sysname, with no default.
[@security_mode = ] security_mode
Is the security mode used when linking to the Publisher. security_mode is int, with no default. If 0, the synchronization triggers use a dynamic RPC connection to the Publisher. If 2, the synchronization triggers use a static sysservers entry to do RPC, and publisher must be defined in the sysservers table as a remote server or linked server.
[@login = ] 'login'
Is the login. login is sysname, with a default of NULL.
[@password = ] 'password'
Is the password. password is sysname, with a default of NULL.
[@distributor = ] 'distributor'
Is the name of the Distributor. distributor is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_link_publication is used by all updatable subscriptions in snapshot replication and transactional replication.
sp_link_publication can be used for both push and pull subscriptions. It can be called before or after the subscription is created. An entry is inserted or updated in the MSsubscription_properties system table. Use sp_helpsubscription_properties to view the values (publisher_security_mode, publisher_login, publisher_password) being set.
For push subscriptions, the entry can be cleaned up by sp_subscription_cleanup. For pull subscriptions, the entry can be cleaned up by sp_droppullsubscription or sp_subscription_cleanup. You can also call sp_link_publisher with a NULL password to clear the entry in the MSsubscription_properties system table for security concerns.
The default mode used by an immediate updating Subscriber when it connects to the Publisher does not allow a connection using Windows Authentication. To connect with a mode of Windows Authentication, a linked server will have to be set up to the Publisher, and the immediate updating Subscriber should use this connection when updating the Subscriber. This requires the sp_link_publication to be run with security_mode = 2.
Permissions
Only members of the sysadmin fixed server role can execute sp_link_publication.