sp_changepublication (Transact-SQL)
Changes the properties of a publication. This stored procedure is executed at the Publisher on the publication database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_changepublication [ [ @publication = ] 'publication' ]
[ , [ @property = ] 'property' ]
[ , [ @value = ] 'value' ]
[ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
[ , [ @force_reinit_subscription = ] force_reinit_subscription ]
[ , [ @publisher = ] 'publisher' ]
Arguments
- [ @publication = ] 'publication'
Is the name of the publication. publication is sysname, with a default of NULL.
- [ @property = ] 'property'
Is the publication property to change. property is nvarchar(255).
[ @value = ] 'value'
Is the new property value. value is nvarchar(255), with a default of NULL.This table describes the properties of the publication that can be changed and restrictions on the values for those properties.
Property
[ @force_invalidate_snapshot = ] force_invalidate_snapshot
Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.0 specifies that changes to the article do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.
1 specifies that changes to the article may cause the snapshot to be invalid. If there are existing subscriptions that would require a new snapshot, this value gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.
See the Remarks section for the properties that, when changed, require the generation of a new snapshot.
[@force_reinit_subscription = ] force_reinit_subscription
Acknowledges that the action taken by this stored procedure may require existing subscriptions to be reinitialized. force_reinit_subscription is a bit with a default of 0.0 specifies that changes to the article do not cause the subscription to be reinitialized. If the stored procedure detects that the change would require existing subscriptions to be reinitialized, an error occurs and no changes are made.
1 specifies that changes to the article cause the existing subscription to be reinitialized, and gives permission for the subscription reinitialization to occur.
[ @publisher = ] 'publisher'
Specifies a non-SQL Server Publisher. publisher is sysname, with a default of NULL.Note
publisher should not be used when changing article properties on a SQL Server Publisher.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_changepublication is used in snapshot replication and transactional replication.
Changing any of the following properties requires that a new snapshot be generated, and you must specify a value of 1 for the force_invalidate_snapshot parameter.
- alt_snapshot_folder
- compress_snapshot
- enabled_for_het_sub
- ftp_address
- ftp_login
- ftp_password
- ftp_port
- ftp_subdirectory
- post_snapshot_script
- pre_snapshot_script
- snapshot_in_defaultfolder
- sync_mode
To list publication objects in the Active Directory using the publish_to_active_directory parameter, the SQL Server object must already be created in the Active Directory.
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_changepublication.
Example
DECLARE @publication AS sysname
SET @publication = N'AdvWorksProductTran'
-- Turn off DDL replication for the transactional publication.
USE [AdventureWorks]
EXEC sp_changepublication
@publication = @publication,
@property = N'replicate_ddl',
@value = 0
GO
See Also
Reference
sp_addpublication (Transact-SQL)
sp_droppublication (Transact-SQL)
sp_helppublication (Transact-SQL)
Replication Stored Procedures (Transact-SQL)
Other Resources
How to: View and Modify Publication Properties (Replication Transact-SQL Programming)
Changing Publication and Article Properties
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|