sp_replicationdboption (Transact-SQL)

Sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

Transact-SQL Syntax Conventions

Syntax

sp_replicationdboption [ @dbname= ] 'db_name' 
        , [ @optname= ] 'optname' 
        , [ @value= ] 'value' 
    [ , [ @ignore_distributor= ] ignore_distributor ]
    [ , [ @from_scripting = ] from_scripting ]

Arguments

• [@dbname=] 'dbname'
  Is the database for which the replication database option is being set. db_name is sysname, with no default.

• [@optname=] 'optname'
  Is the replication database option to enable or disable. optname is sysname, and can be one of these values.

  Value	Description
  merge publish	Database can be used for merge publications.
  publish	Database can be used for other types of publications.
  subscribe	Database is a subscription database.
  sync with backup	Database is enabled for coordinated backup. For more information, see How to: Enable Coordinated Backups for Transactional Replication (Replication Transact-SQL Programming).

• [ @value=] 'value'
  Is whether to enable or disable the given replication database option. value is sysname, and can be true or false. When this value is false and optname is merge publish, subscriptions to the merge published database are also dropped.

• [ @ignore_distributor=] ignore_distributor
  Indicates whether this stored procedure is executed without connecting to the Distributor. ignore_distributor is bit, with a default of 0, meaning the Distributor should be connected to and updated with the new status of the publishing database. The value 1 should be specified only if the Distributor is inaccessible and sp_replicationdboption is being used to disable publishing.

• [ @from_scripting=] from_scripting
  For internal use only.

Return Code Values

0 (success) or 1 (failure)

Remarks

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

This procedure creates or drops specific replication system tables, security accounts, and so on, depending on the options given. Sets the corresponding category bit in the master.sysdatabases system table and creates the necessary system tables.

To disable publishing, the publication database must be online. If a database snapshot exists for the publication database, it must be dropped before disabling publishing. A database snapshot is a read-only offline copy of a database, and is not related to a replication snapshot. For more information, see Database Snapshots.

Permissions

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

See Also

Reference

sys.sysdatabases (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Other Resources

How to: Configure Publishing and Distribution (Replication Transact-SQL Programming)
How to: Create a Publication (Replication Transact-SQL Programming)
How to: Delete a Publication (Replication Transact-SQL Programming)
How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance