Transact-SQL Reference

sp_addpublication

Creates a snapshot or transactional publication. This stored procedure is executed at the Publisher on the publication database.

Syntax

sp_addpublication [ @publication = ] 'publication'
    [ , [ @taskid = ] tasked ]
    [ , [ @restricted = ] 'restricted' ]
    [ , [ @sync_method = ] 'sync_method' ]
    [ , [ @repl_freq = ] 'repl_freq' ]
    [ , [ @description = ] 'description' ]
    [ , [ @status = ] 'status' ]
    [ , [ @independent_agent = ] 'independent_agent' ]
    [ , [ @immediate_sync = ] 'immediate_sync' ]
    [ , [ @enabled_for_internet = ] 'enabled_for_internet' ]
    [ , [ @allow_push = ] 'allow_push'
    [ , [ @allow_pull = ] 'allow_pull' ]
    [ , [ @allow_anonymous = ] 'allow_anonymous' ]
    [ , [ @allow_sync_tran = ] 'allow_sync_tran' ]
    [ , [ @autogen_sync_procs = ] 'autogen_sync_procs' ]
    [ , [ @retention = ] retention ]
    [ , [ @allow_queued_tran= ] 'allow_queued_updating' ]
    [ , [ @snapshot_in_defaultfolder= ] 'snapshot_in_default_folder' ]
    [ , [ @alt_snapshot_folder= ] 'alternate_snapshot_folder' ]
    [ , [ @pre_snapshot_script= ] 'pre_snapshot_script' ]
    [ , [ @post_snapshot_script= ] 'post_snapshot_script' ]
    [ , [ @compress_snapshot= ] 'compress_snapshot' ]
    [ , [ @ftp_address = ] 'ftp_address' ]
    [ , [ @ftp_port= ] ftp_port ]
    [ , [ @ftp_subdirectory = ] 'ftp_subdirectory' ]
    [ , [ @ftp_login = ] 'ftp_login' ]
    [ , [ @ftp_password = ] 'ftp_password' ]
    [ , [ @allow_dts = ] 'allow_transformable_subscriptions' ]
    [ , [ @allow_subscription_copy = ] 'allow_subscription_copy' ]
    [ , [ @conflict_policy = ] 'conflict_policy' ]
    [ , [ @centralized_conflicts = ] 'centralized_conflicts' ]
    [ , [ @conflict_retention = ] conflict_retention ]
    [ , [ @queue_type = ] 'queue_type' ]
    [ , [ @add_to_active_directory = ] 'add_to_active_directory' ]
    [ , [ @logreader_job_name = ] 'logreader_agent_name' ]
    [ , [ @qreader_job_name = ] 'queue_reader_agent_name' ]

Arguments

[@publication = ] 'publication'

Is the name of the publication to create. publication is sysname, with no default. The name must be unique within the database.

[@taskid = ] taskid

Supported for backward compatibility only; use sp_addpublication_snapshot.

[@restricted = ] 'restricted'

Supported for backward compatibility only; use default_access.

[@sync_method = ] 'sync_method'

Is the synchronization mode. sync_method is nvarchar(13), and can be one of these values.

Value	Description
native (default)	Produces native-mode bulk copy program output of all tables.
character	Produces character-mode bulk copy program output of all tables.
concurrent	Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot.
concurrent_c	Produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot.

Note The values concurrent and concurrent_c are available for transactional and merge replication, but not snapshot replication.

[@repl_freq = ] 'repl_freq'

Is the type of replication frequency. replication_frequency is nvarchar(10), with a default of continuous. If continuous, the Publisher provides output of all log-based transactions. If Snapshot, the Publisher produces only scheduled synchronization events.

[@description = ] 'description'

Is an optional description for the publication. description is nvarchar(255), with a default of NULL.

[@status = ] 'status'

Specifies if publication data is available. status is nvarchar(8), and can be one of these values.

Value	Description
active	Publication data is available for Subscribers immediately.
inactive (default)	Publication data is not available for Subscribers when the publication is first created (they can subscribe, but the subscriptions are not processed).

[@independent_agent = ] 'independent_agent'

Specifies if there is a stand-alone Distribution Agent for this publication. independent_agent is nvarchar(5), with a default of FALSE. If true, there is a stand-alone Distribution Agent for this publication. If false, the publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a single, shared Agent.

[@immediate_sync = ] 'immediate_synchronization'

Specifies if the synchronization files for the publication are created each time the Snapshot Agent runs. immediate_synchronization is nvarchar(5), with a default of FALSE. If true, the synchronization files are created or re-created each time the Snapshot Agent runs. Subscribers are able to get the synchronization files immediately if the Snapshot Agent has completed before the subscription is created. New subscriptions get the newest synchronization files generated by the most recent execution of the Snapshot Agent. independent_agent must be true for immediate_synchronization to be true. If false, the synchronization files are created only if there are new subscriptions. Subscribers cannot receive the synchronization files after the subscription until the Snapshot Agents are started and completed.

[@enabled_for_internet = ] 'enabled_for_internet'

Specifies if the publication is enabled for the Internet, and determines if FTP can be use to transfer the snapshot files to a subscriber. enabled_for_internet is nvarchar(5), with a default of FALSE. If true, the synchronization files for the publication are put into the C:\Program Files\Microsoft SQL Server\MSSQL\Repldata\Ftp directory. The user must create the Ftp directory.

[@allow_push = ] 'allow_push'

Specifies if push subscriptions can be created for the given publication. allow_push is nvarchar(5), with a default of TRUE, which allows push subscriptions on the publication.

[@allow_pull = ] 'allow_pull'

Specifies if pull subscriptions can be created for the given publication. allow_pull is nvarchar(5), with a default of FALSE. If false, pull subscriptions are not allowed on the publication.

[@allow_anonymous = ] 'allow_anonymous'

Specifies if anonymous subscriptions can be created for the given publication. allow_anonymous is nvarchar(5), with a default of FALSE. If true, immediate_synchronization must also be set to true. If false, anonymous subscriptions are not allowed on the publication.

[@allow_sync_tran = ] 'allow_sync_tran'

Specifies if immediate-updating subscriptions are allowed on the publication. allow_sync_tran is nvarchar(5), with a default of FALSE.

[@autogen_sync_procs = ] 'autogen_sync_procs'

Specifies if the synchronizing stored procedure for immediate-updating subscriptions is generated at the Publisher. autogen_sync_procs is nvarchar(5), with a default of TRUE.

[@retention = ] retention]

Is the retention period in hours for subscription activity. retention is int, with a default of 336 hours. If a subscription is not active within the retention period, it expires and is removed. The value can be greater than the maximum retention period of the distribution database used by the Publisher. If 0, well-known subscriptions to the publication will never expire and be removed by the Expired Subscription Cleanup Agent. For more information, see Subscription Deactivation and Expiration.

[@allow_queued_tran = ] 'allow_queued_updating'

Enables or disables queuing of changes at the Subscriber until they can be applied at the Publisher. allow_queued_updating is nvarchar(5) with a default of FALSE. If false, changes at the Subscriber are not queued.

[@snapshot_in_defaultfolder = ] 'snapshot_in_default_folder'

Specifies if snapshot files are stored in the default folder. snapshot_in_default_folder is nvarchar(5) with a default of TRUE. If true, snapshot files can be found in the default folder. If false, snapshot files have been stored in the alternate location specified by alternate_snapshot_folder. Alternate locations can be on another server, on a network drive, or on removable media (such as CD-ROM or removable disks). You can also save the snapshot files to a File Transfer Protocol (FTP) site, for retrieval by the Subscriber at a later time. Note that this parameter can be true and still have a location in the @alt_snapshot_folder parameter. This combination specifies that the snapshot files will be stored in both the default and alternate locations.

[@alt_snapshot_folder = ] 'alternate_snapshot_folder'

Specifies the location of the alternate folder for the snapshot. alternate_snapshot_folder is nvarchar(255) with a default of NULL.

[@pre_snapshot_script = ] 'pre_snapshot_script'

Specifies a pointer to an .sql file location. pre_snapshot_script is nvarchar(255), with a default of NULL. The Distribution Agent will run the pre-snapshot script before running any of the replicated object scripts when applying a snapshot at a Subscriber.

[@post_snapshot_script = ] 'post_snapshot_script'

Specifies a pointer to an .sql file location. post_snapshot_script isnvarchar(255), with a default of NULL. The Distribution Agent will run the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization.

[@compress_snapshot = ] 'compress_snapshot'

Specifies that the snapshot that is written to the @alt_snapshot_folder location is to be compressed into the Microsoft® CAB format. compress_snapshot is nvarchar(5), with a default of FALSE. false specifies that the snapshot will not be compressed; true specifies that the snapshot will be compressed. The snapshot in the default folder cannot be compressed.

[@ftp_address = ] 'ftp_address'

Is the network address of the FTP service for the Distributor. ftp_address is sysname, with a default of NULL. Specifies where publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up. Since this property is stored for each publication, each publication can have a different ftp_address. The publication must support propagating snapshots using FTP. For more information, see Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP.

[@ftp_port = ] ftp_port

Is the port number of the FTP service for the Distributor. ftp_port is int, with a default of 21. Specifies where the publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up. Since this property is stored for each publication, each publication can have its own ftp_port.

[@ftp_subdirectory = ] 'ftp_subdirectory'

Specifies where the snapshot files will be available for the Distribution Agent or Merge Agent of subscriber to pick up if the publication supports propagating snapshots using FTP. ftp_subdirectory is nvarchar(255), with a default of NULL. Since this property is stored for each publication, each publication can have its own ftp_subdirctory or choose to have no subdirectory, indicated with a NULL value.

[@ftp_login = ] 'ftp_login'

Is the username used to connect to the FTP service. ftp_login is sysname, with a default of ANONYMOUS.

[@ftp_password = ] 'ftp_password'

Is the user password used to connect to the FTP service. ftp_password is sysname, with a default of NULL.

[@allow_dts = ] 'allow_transformable_subscriptions'

Specifies that the publication allows data transformations. You can specify a DTS package when creating a subscription. allow_transformable_subscriptions is nvarchar(5) with a default of FALSE, which does not allow DTS transformations.

[@allow_subscription_copy = ] 'allow_subscription_copy'

Enables or disables the ability to copy the subscription databases that subscribe to this publication. allow_subscription_copy is nvarchar(5), with a default of FALSE.

[@conflict_policy = ] 'conflict_policy'

Specifies the conflict resolution policy followed when the queued updating subscriber option is used. conflict_policy is nvarchar(100) with a default of NULL, and can be one of these values.

Value	Description
pub wins	Publisher wins the conflict.
sub reinit	Reinitialize the subscription.
sub wins	Subscriber wins the conflict.
NULL (default)	If NULL, and the publication is a snapshot publication, the default policy becomes sub reinit. If NULL and the publication is not a snapshot publication, the default becomes pub wins.

[@centralized_conflicts = ] 'centralized_conflicts'

Specifies if conflict records are stored on the Publisher. centralized_conflicts is nvarchar(5), with a default of TRUE. If true, conflict records are stored at the Publisher. If false, conflict records are stored at both the publisher and at the subscriber that caused the conflict.

[@conflict_retention = ] conflict_retention

Specifies the conflict retention period, in days. conflict_retention is int, with a default of 14.

[@queue_type = ] 'queue_type'

Specifies which type of queue is used. queue_type is nvarchar(10), with a default of NULL, and can be one of these values.

Value	Description
msmq	Use Microsoft Message Queuing to store transactions.
sql	Use SQL Server to store transactions.
NULL (default)	Defaults to sql, which specifies to use SQL Server to store transactions.

[@add_to_active_directory = ] 'add_to_active_directory'

Specifies if the publication information is published to the Microsoft Active Directory™. add_to_active_directory is nvarchar(10), with a default of FALSE. This feature is available only for servers running the Microsoft Windows® 2000 operating system.

[@logreader_job_name = ] 'logreader_agent_name'

For internal use only.

[@qreader_job_name = ] 'queue_reader_agent_name'

For internal use only.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addpublication is used in snapshot replication and transactional replication.

To list publication objects to the Active Directory using the @add_to_active_directory parameter, the SQL Server object must already be created in the Active Directory. For more information, see Active Directory Services.

Permissions