sp_changearticle (Transact-SQL)

Changes the properties of an article in a transactional or snapshot publication. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL Syntax Conventions

Syntax

sp_changearticle [ [@publication= ] 'publication' ]
    [ , [ @article= ] 'article' ]
    [ , [ @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 that contains the article. publication is sysname, with a default of NULL.

• [ @article=] 'article'
  Is the name of the article whose property is to be changed. article is sysname, with a default of NULL.

• [ @property=] 'property'
  Is an article property to change. property is nvarchar(100).

• [ @value=] 'value'
  Is the new value of the article property. value is nvarchar(255).

  This table describes the properties of articles and 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, and if there are existing subscriptions that would require a new snapshot, 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 existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

  See the Remarks section for the properties that, when changed, require that all existing subscriptions be reinitialized.

• [ @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_changearticle is used in snapshot replication and transactional replication.

When an article belongs to a publication that supports peer-to-peer transactional replication, you can only change the description, ins_cmd, upd_cmd, and del_cmd properties.

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:

• del_cmd
• dest_table
• destination_owner
• ins_cmd
• pre_creation_cmd
• schema_options
• upd_cmd

Changing any of the following properties requires that existing subscriptions be reinitialized, and you must specify a value of 1 for the force_reinit_subscription parameter.

• del_cmd
• dest_table
• destination_owner
• filter
• ins_cmd
• status
• upd_cmd

Within an existing publication, you can use sp_changearticle to change an article without having to drop and re-create the entire publication.

Note

When changing the value of schema_option, the system does not perform a bitwise update. This means that when you set schema_option using sp_changearticle, existing bit settings may be turned off. To retain the existing settings, you should perform & (Bitwise AND) between the value that you are setting and the current value of schema_option, which can be determined by executing sp_helparticle.

Valid Schema Options

The following table describes the allowable values of schema_option based upon the replication type (shown across the top) and the article type (shown down the first column).

Article type	Replication type
	Transactional	Snapshot
logbased	All options	All options but 0x02
logbased manualfilter	All options	All options but 0x02
logbased manualview	All options	All options but 0x02
indexed view logbased	All options	All options but 0x02
indexed view logbased manualfilter	All options	All options but 0x02
indexed view logbased manualview	All options	All options but 0x02
indexed view logbase manualboth	All options	All options but 0x02
proc exec	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000
serialized proc exec	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000
proc schema only	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000
view schema only	0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000	0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000
func schema only	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000	0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000
indexed view schema only	0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000	0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000

Note

For queued updating publications, the schema_option value of 0x80 must be enabled. The supported schema_option values for non-SQL Server publications are: 0x01, 0x02, 0x10, 0x40, 0x80, 0x1000 and 0x4000.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_changearticle.

Example

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @option AS int;
SET @publication = N'AdvWorksProductTran';
SET @article = N'Product';
SET @option = (SELECT CAST(0x0000000002030073 AS int));

-- Change the schema options to replicate schema with XML.
USE [AdventureWorks]
EXEC sp_changearticle 
  @publication = @publication,
  @article = @article, 
  @property = N'schema_option', 
  @value = @option,
  @force_invalidate_snapshot = 1;
GO

See Also

Reference

sp_addarticle (Transact-SQL)
sp_articlecolumn (Transact-SQL)
sp_droparticle (Transact-SQL)
sp_helparticle (Transact-SQL)
sp_helparticlecolumns (Transact-SQL)

Other Resources

How to: View and Modify Article Properties (Replication Transact-SQL Programming)
Changing Publication and Article Properties

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Updated the description of the 0x20000000 value of the schema_option property.
5 December 2005	New content: Added missing values for the type property. Documented additional restrictions when setting the 0x20 option for the schema_option parameter. Changed content: Clarified descriptions for the type property. Corrected the size of the property parameter.