Skip to content

Latest commit

 

History

History
144 lines (98 loc) · 6.47 KB

sp-adddistpublisher-transact-sql.md

File metadata and controls

144 lines (98 loc) · 6.47 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_adddistpublisher (Transact-SQL)
Configures a Publisher to use a specified distribution database.
mashamsft
mathoma
randolphwest
11/02/2023
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. 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 = ] N'publisher'
    , [ @distribution_db = ] N'distribution_db'
    [ , [ @security_mode = ] security_mode ]
    [ , [ @login = ] N'login' ]
    [ , [ @password = ] N'password' ]
    [ , [ @working_directory = ] N'working_directory' ]
    [ , [ @trusted = ] N'trusted' ]
    [ , [ @encrypted_password = ] encrypted_password ]
    [ , [ @thirdparty_flag = ] thirdparty_flag ]
    [ , [ @publisher_type = ] N'publisher_type' ]
    [ , [ @storage_connection_string = ] N'storage_connection_string' ]
[ ; ]

Arguments

[ @publisher = ] N'publisher'

The Publisher name. @publisher is sysname, with no default.

::: moniker range=">= sql-server-linux-ver15 || >= sql-server-ver15" [!INCLUDE custom-port] ::: moniker-end

[ @distribution_db = ] N'distribution_db'

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

[ @security_mode = ] security_mode

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

Value Description
0 Replication agents at the Distributor use [!INCLUDE ssNoVersion] Authentication to connect to the Publisher.
1 (default) Replication agents at the Distributor use Windows Authentication to connect to the Publisher.

[ @login = ] N'login'

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 = ] N'password'

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

Important

Don't use a blank password. Use a strong password.

[ @working_directory = ] N'working_directory'

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

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

[ @trusted = ] N'trusted'

@trusted is deprecated, and is provided for backward compatibility only. @trusted is nvarchar(5), with a default of false. Setting this parameter to anything but false results in an error.

[ @encrypted_password = ] encrypted_password

Setting this parameter is no longer supported. @encrypted_password is bit, with a default of 0. Setting this parameter to 1 results in an error.

[ @thirdparty_flag = ] thirdparty_flag

Specifies when the Publisher is [!INCLUDE ssNoVersion]. @thirdparty_flag is bit, and can be one of the following values.

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

[ @publisher_type = ] N'publisher_type'

Specifies the Publisher type when the Publisher isn't [!INCLUDE ssNoVersion]. @publisher_type is sysname, and can be one of the following values.

Value Description
MSSQLSERVER (default) Specifies a [!INCLUDE ssNoVersion] 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.

[ @storage_connection_string = ] N'storage_connection_string'

Required for Azure SQL Database. @storage_connection_string is nvarchar(255), with a default of NULL. Use the access key from the Azure portal, under Storage > Settings.

[!INCLUDE Azure SQL Database link]

Return code values

0 (success) or 1 (failure).

Remarks

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

Examples

:::code language="sql" source="../replication/codesnippet/tsql/sp-adddistpublisher-tran_1.sql":::

Permissions

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

Related content