sp_attachsubscription (Transact-SQL)

  • Article

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 should not 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 Snapshots for Merge Publications with Parameterized Filters. For publications that are not partitioned, you can initialize a subscription with a backup. For more information, see Initialize a Transactional Subscription Without a Snapshot.

Topic link icon Transact-SQL Syntax Conventions

Syntax


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

Arguments

  • [ @dbname= ] 'dbname'
    Is the string that specifies the destination subscription database by name. dbname is sysname, with no default.

  • [ @filename= ] 'filename'
    Is the name and physical location of the primary MDF (master data file). filename is nvarchar(260), with no default.

  • [ @subscriber_security_mode= ] 'subscriber_security_mode'
    Is 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 is not 1 (Windows Authentication), an error is returned.

  • [ @subscriber_login= ] 'subscriber_login'
    Is 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 has been deprecated and is maintained only backward-compatibility of scripts. If subscriber_security_mode is not 1 and subscriber_login is specified, an error is returned.

  • [ @subscriber_password= ] 'subscriber_password'
    Is the Subscriber password. subscriber_password is sysname, with a default of NULL.

    Note

    This parameter has been deprecated and is maintained only backward-compatibility of scripts. If subscriber_security_mode is not 1 and subscriber_password is specified, an error is returned.

  • [ @distributor_security_mode= ] distributor_security_mode
    Is the security mode to use when connecting to a Distributor when synchronizing. distributor_security_mode is int, with a default of 0. 0 specifies SQL Server Authentication. 1 specifies Windows Authentication. When possible, use Windows Authentication.

  • [ @distributor_login= ] 'distributor_login'
    Is the Distributor login to use when connecting to a Distributor when synchronizing. distributor_login is required if distributor_security_mode is set to 0. distributor_login is sysname, with a default of NULL.

  • [ @distributor_password= ] 'distributor_password'
    Is the Distributor password. distributor_password is required if distributor_security_mode is set to 0. distributor_password is sysname, with a default of NULL. The value of distributor_password must be less than 120 Unicode characters.

    Security note Security Note

    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
    Is 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= ] 'publisher_login'
    Is the login to use when connecting to a Publisher when synchronizing. publisher_login is sysname, with a default of NULL.

  • [ @publisher_password= ] 'publisher_password'
    Is the password used when connecting to the Publisher. publisher_password is sysname, with a default of NULL. The value of publisher_password must be less than 120 Unicode characters.

    Security note Security Note

    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= ] 'job_login'
    Is 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= ] 'job_password'
    Is the password for the Windows account under which the agent runs. job_password is sysname, with no default. The value of job_password must be less than 120 Unicode characters.

    Security note Security Note

    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= ] 'db_master_key_password'
    Is the password of a user-defined Database Master Key. db_master_key_password is nvarchar(524), with a default value of NULL. If db_master_key_password is not specified, an existing Database Master Key will be dropped and re-created.

    Security note Security Note

    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 cannot 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 either when the subscription is attached or when it is 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.

See Also

Reference

System Stored Procedures (Transact-SQL)