sp_link_publication (Transact-SQL)
Sets the configuration and security information used by synchronization triggers of immediate updating subscriptions when connecting to the Publisher. This stored procedure is executed at the Subscriber on the subscription database.
.gif "Security note") Security Note |
|---|
When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server. |
Important
Under certain conditions, this stored procedure can fail if the Subscriber is running Microsoft SQL Server 2005 Service Pack 1 or later, and the Publisher is running an earlier version. If the stored procedure fails in this scenario, upgrade the Publisher to SQL Server 2005 Service Pack 1 or later.
.gif "Topic link icon")
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 by the Subscriber to connect to a remote Publisher for immediate updating. security_mode is int, and can be one of these values. When possible, use Windows Authentication.Value
Description
0
Uses SQL Server Authentication with the login specified in this stored procedure as login and password.
.gif "Note")
Note
In previous versions of SQL Server, this option was used to specify a dynamic remote procedure call (RPC).1
Uses the security context (SQL Server Authentication or Windows Authentication) of the user making the change at the Subscriber.
Note
This account must also exist at the Publisher with sufficient privileges. When using Windows Authentication, security account delegation must be supported.2
Uses an existing, user-defined linked server login created using sp_link_publication.
[ @login= ] 'login'
Is the login. login is sysname, with a default of NULL. This parameter must be specified when security_mode is 0.[ @password= ] 'password'
Is the password. password is sysname, with a default of NULL. This parameter must be specified when security_mode is 0.[ @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 immediate updating subscriptions in 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 (Transact-SQL) system table.
For push subscriptions, the entry can be cleaned up by sp_subscription_cleanup (Transact-SQL). For pull subscriptions, the entry can be cleaned up by sp_droppullsubscription (Transact-SQL) or sp_subscription_cleanup (Transact-SQL). You can also call sp_link_publication with a NULL password to clear the entry in the MSsubscription_properties (Transact-SQL) 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 has 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. When using Windows Authentication, security account delegation must be supported.
Example
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
-- Execute this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'AdvWorksProductTran';
SET @publicationDB = N'AdventureWorks2008R2';
SET @publisher = $(PubServer);
SET @login = $(Login);
SET @password = $(Password);
-- At the subscription database, create a pull subscription to a transactional
-- publication using immediate updating with queued updating as a failover.
EXEC sp_addpullsubscription
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB,
@update_mode = N'failover',
@subscription_type = N'pull';
-- Add an agent job to synchronize the pull subscription,
-- which uses Windows Authentication when connecting to the Distributor.
EXEC sp_addpullsubscription_agent
@publisher = @publisher,
@publisher_db = @publicationDB,
@publication = @publication,
@job_login = @login,
@job_password = @password;
-- Add a Windows Authentication-based linked server that enables the
-- Subscriber-side triggers to make updates at the Publisher.
EXEC sp_link_publication
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB,
@security_mode = 0,
@login = @login,
@password = @password;
GO
USE AdventureWorks2008R2;
GO
-- Execute this batch at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriptionDB = N'AdventureWorks2008R2Replica';
SET @subscriber = $(SubServer);
-- At the Publisher, register the subscription, using the defaults.
USE [AdventureWorks2008R2]
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@subscription_type = N'pull',
@update_mode = N'failover';
GO
Permissions
Only members of the sysadmin fixed server role can execute sp_link_publication.