This topic has not yet been rated - Rate this topic

sp_adddistpublisher (Transact-SQL)

SQL Server 2005

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

Syntax


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' ]

Arguments

[ @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.

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

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.

Return Code Values

0 (success) or 1 (failure)

Remarks

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

Permissions

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

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

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

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

sp_adddistpublisher (Transact-SQL)

Reference

Other Resources

Help and Information

Community Additions