title

description

author

ms.author

ms.date

ms.service

ms.subservice

ms.topic

f1_keywords

helpviewer_keywords

dev_langs

sp_adddistpublisher (Transact-SQL)

mashamsft

mathoma

03/29/2021

sql

replication

reference

sp_adddistpublisher

sp_adddistpublisher_TSQL

sp_adddistpublisher

TSQL

sp_adddistpublisher (Transact-SQL)

[!INCLUDE SQL Server SQL MI]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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' ]   
    [ , [ @storage_connection_string= ] 'storage_connection_string']
    [ , [ @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.

::: moniker range=">= sql-server-linux-ver15 || >= sql-server-ver15 "

[!NOTE] Server name can be specified as <Hostname>,<PortNumber>. You may need to specify the port number for your connection when SQL Server is deployed on Linux or Windows with a custom port, and browser service is disabled. The use of custom port numbers for remote distributor applies to SQL Server 2019 only.

::: moniker-end

[ @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- [!INCLUDEssNoVersion] Publisher. security_mode is int, and can be one of these values.

Value	Description
0	Replication agents at the Distributor use [!INCLUDEssNoVersion] 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.

[!IMPORTANT]
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 [!INCLUDEssNoVersion], for example C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData. The name should be specified in UNC format.

For Azure SQL Database, use \\<storage_account>.file.core.windows.net\<share>.

[ @storage_connection_string = ] 'storage_connection_string' Is required for SQL Database. Use the access key from Azure Portal under storage > settings.

[!INCLUDEAzure SQL Database link]

[ @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 [!INCLUDEssNoVersion]. thirdparty_flag is bit, and can be one of the following values.

Value	Description
0 (default)	[!INCLUDEssNoVersion] database.
1	Database other than [!INCLUDEssNoVersion].

[ @publisher_type = ] 'publisher_type' Specifies the Publisher type when the Publisher is not [!INCLUDEssNoVersion]. publisher_type is sysname, and can be one of the following values.

Value	Description
MSSQLSERVER (default)	Specifies a [!INCLUDEssNoVersion] 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 Configure an Oracle Publisher.

Return Code Values

0 (success) or 1 (failure)

Remarks

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

Example

[!code-sqlHowTo#AddDistPub]

Permissions

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

sql-docs/docs/relational-databases/system-stored-procedures/sp-adddistpublisher-transact-sql.md

sp_adddistpublisher (Transact-SQL)

Syntax

Arguments

Return Code Values

Remarks

Example

Permissions

See Also