Export (0) Print
Expand All
Expand Minimize

sp_adddistpublisher (Transact-SQL)

Configures a Publisher to use a specified distribution database. This stored procedure is executed at the Distributor on any database. Note that the stored procedures sp_adddistributor (Transact-SQL) and sp_adddistributiondb (Transact-SQL) must have been run prior to using this stored procedure.

Topic link icon Transact-SQL Syntax Conventions


sp_adddistpublisher [ @publisher= ] 'publisher' 
        , [ @distribution_db= ] 'distribution_db' 
    [ , [ @security_mode= ] security_mode ] 
    [ , [ @login= ] 'login' ] 
    [ , [ @password= ] 'password' ] 
    [ , [ @working_directory= ] 'working_directory' ] 
    [ , [ @trusted= ] 'trusted' ] 
    [ , [ @encrypted_password= ] encrypted_password ] 
    [ , [ @thirdparty_flag = ] thirdparty_flag ]
    [ , [ @publisher_type = ] 'publisher_type' ]

[ @publisher=] 'publisher'

Is the Publisher name. publisher is sysname, with no default.

[ @distribution_db=] 'distribution_db'

Is the name of the distribution database. distributor_db is sysname, with no default. This parameter is used by replication agents to connect to the Publisher.

[ @security_mode=] security_mode

Is the implemented security mode. This parameter is only used by replication agents to connect to the Publisher for queued updating subscriptions or with a non-SQL Server Publisher. security_mode is int, and can be one of these values.

Value Description

0

Replication agents at the Distributor use SQL Server Authentication to connect to the Publisher.

1 (default)

Replication agents at the Distributor use Windows Authentication to connect to the Publisher.

[ @login=] 'login'

Is the login. This parameter is required if security_mode is 0. login is sysname, with a default of NULL. This parameter is used by replication agents to connect to the Publisher.

[ @password=] 'password']

Is the password. password is sysname, with a default of NULL. This parameter is used by replication agents to connect to the Publisher.

ms173807.security(en-US,SQL.90).gifSecurity Note:
Do not use a blank password. Use a strong password.

[ @working_directory=] 'working_directory'

Is the name of the working directory used to store data and schema files for the publication. working_directory is nvarchar(255), and defaults to the ReplData folder for this instance of SQL Server, for example 'C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData'. The name should be specified in UNC format.

[ @trusted=] 'trusted'

This parameter has been deprecated and is provided for backward compatibility only. trusted is nvarchar(5), and setting it to anything but false will result in an error.

[ @encrypted_password=] encrypted_password

Setting encrypted_password is no longer supported. Attempting to set this bit parameter to 1 will result in an error.

[ @thirdparty_flag =] thirdparty_flag

Is when the Publisher is SQL Server. thirdparty_flag is bit, and can be one of the following values.

Value Description

0 (default)

SQL Server database.

1

Database other than SQL Server.

[ @publisher_type= ] 'publisher_type'

Specifies the Publisher type when the Publisher is not SQL Server. publisher_type is sysname, and can be one of the following values.

Value Description

MSSQLSERVER 

(default)

Specifies a SQL Server Publisher.

ORACLE

Specifies a standard Oracle Publisher.

ORACLE GATEWAY

Specifies an Oracle Gateway Publisher.

For more information about the differences between an Oracle Publisher and an Oracle Gateway Publisher, see Configuring an Oracle Publisher.

0 (success) or 1 (failure)

sp_adddistpublisher is used by snapshot replication, transactional replication, and merge replication.

Only members of the sysadmin fixed server role can execute sp_adddistpublisher.

-- 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".

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
-- Specify the replication working directory.
SET @directory = N'\\' + $(DistPubServer) + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'AdventureWorks'; 

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB, 
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher, 
    @distribution_db=@distributionDB, 
    @security_mode = 1;
GO 

Community Additions

ADD
Show:
© 2014 Microsoft