Edit

Share via

sp_attachsubscription (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database

Attaches an existing subscription database to any Subscriber. This stored procedure is executed at the new Subscriber on the master database.

Important

This feature is deprecated and will be removed in a future release. This feature shouldn't be used in new development work. For merge publications that are partitioned using parameterized filters, we recommend using the new features of partitioned snapshots, which simplify the initialization of a large number of subscriptions. For more information, see Create a Snapshot for a Merge Publication with Parameterized Filters. For publications that aren't partitioned, you can initialize a subscription with a backup. For more information, see Initialize a Transactional Subscription Without a Snapshot.

Transact-SQL syntax conventions

Syntax

sp_attachsubscription
    [ @dbname = ] N'dbname'
    , [ @filename = ] N'filename'
    [ , [ @subscriber_security_mode = ] subscriber_security_mode ]
    [ , [ @subscriber_login = ] N'subscriber_login' ]
    [ , [ @subscriber_password = ] N'subscriber_password' ]
    [ , [ @distributor_security_mode = ] distributor_security_mode ]
    [ , [ @distributor_login = ] N'distributor_login' ]
    [ , [ @distributor_password = ] N'distributor_password' ]
    [ , [ @publisher_security_mode = ] publisher_security_mode ]
    [ , [ @publisher_login = ] N'publisher_login' ]
    [ , [ @publisher_password = ] N'publisher_password' ]
    [ , [ @job_login = ] N'job_login' ]
    [ , [ @job_password = ] N'job_password' ]
    [ , [ @db_master_key_password = ] N'db_master_key_password' ]
[ ; ]

Arguments

[ @dbname = ] N'dbname'

Specifies the name of the destination subscription database. @dbname is sysname, with no default.

[ @filename = ] N'filename'

The name and physical location of the primary data file (.mdf). @filename is nvarchar(260), with no default.

[ @subscriber_security_mode = ] subscriber_security_mode

The security mode of the Subscriber to use when connecting to a Subscriber when synchronizing. @subscriber_security_mode is int, with a default of NULL.

Note

Windows Authentication must be used. If @subscriber_security_mode isn't 1 (Windows Authentication), you receive an error.

[ @subscriber_login = ] N'subscriber_login'

The Subscriber login name to use when connecting to a Subscriber when synchronizing. @subscriber_login is sysname, with a default of NULL.

Note

This parameter is deprecated and is maintained for backward compatibility of scripts. If @subscriber_security_mode isn't 1 and @subscriber_login is specified, you receive an error.

[ @subscriber_password = ] N'subscriber_password'

The Subscriber password. @subscriber_password is sysname, with a default of NULL.

Note

This parameter is deprecated and is maintained for backward compatibility of scripts. If @subscriber_security_mode isn't 1 and @subscriber_password is specified, you receive an error.

[ @distributor_security_mode = ] distributor_security_mode

The security mode to use when connecting to a Distributor when synchronizing. @distributor_security_mode is int, with a default of 1.

  • 0 specifies SQL Server authentication
  • 1 specifies Windows authentication

When possible, use Windows authentication.

[ @distributor_login = ] N'distributor_login'

The Distributor login to use when connecting to a Distributor when synchronizing. @distributor_login is sysname, with a default of NULL. @distributor_login is required if @distributor_security_mode is set to 0.

[ @distributor_password = ] N'distributor_password'

The Distributor password. @distributor_password is sysname, with a default of NULL. @distributor_password is required if @distributor_security_mode is set to 0. The value of @distributor_password must be shorter than 120 Unicode characters.

Important

Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

[ @publisher_security_mode = ] publisher_security_mode

The security mode to use when connecting to a Publisher when synchronizing. @publisher_security_mode is int, with a default of 1.

  • If 0, specifies SQL Server Authentication.
  • If 1, specifies Windows Authentication. When possible, use Windows authentication.

[ @publisher_login = ] N'publisher_login'

The login to use when connecting to a Publisher when synchronizing. @publisher_login is sysname, with a default of NULL.

[ @publisher_password = ] N'publisher_password'

The password used when connecting to the Publisher. @publisher_password is sysname, with a default of NULL. The value of @publisher_password must be shorter than 120 Unicode characters.

Important

Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

[ @job_login = ] N'job_login'

The login for the Windows account under which the agent runs. @job_login is nvarchar(257), with no default. This Windows account is always used for agent connections to the Distributor.

[ @job_password = ] N'job_password'

The password for the Windows account under which the agent runs. @job_password is sysname, with a default of NULL. The value of job_password must be shorter than 120 Unicode characters.

Important

Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

[ @db_master_key_password = ] N'db_master_key_password'

The password of a user-defined database master key (DMK). @db_master_key_password is nvarchar(524), with a default of NULL. If @db_master_key_password isn't specified, an existing DMK is dropped and recreated.

Important

Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

Return code values

0 (success) or 1 (failure).

Remarks

sp_attachsubscription is used in snapshot replication, transactional replication, and merge replication.

A subscription can't be attached to the publication if the publication retention period has expired. If a subscription with an elapsed retention period is specified, an error occurs when the subscription is either attached, or first synchronized. Publications with a publication retention period of 0 (never expire) are ignored.

Permissions

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

Related content