sp_changearticle (T-SQL)

Changes the properties of an article.

Syntax

sp_changearticle [[@publication =] 'publication'] [,[@article =]'article']
    [,[@property =] 'property'] [,[@value =] 'value']

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(20).

[@value =] 'value'

Is the new value of the article property. value is nvarchar(255).

This table describes the valid properties of articles and the values for those properties.

 

Property	Values	Description
description	description	New descriptive entry for the publication job.
sync_object	sync_object	Name of the table or view used to produce a synchronization output file. The default is NULL.
type		Article type. Can have a value of:
	logbased (default)	Log-based article.
	logbased manualfilter	Log-based article with manual filter.
	logbased manualview	Log-based article with manual view.
	logbased manualboth	Log-based article with both manual filter and manual view.
ins_cmd	ins_cmd	INSERT statement to execute; otherwise, it is constructed from the log.
del_cmd	del_cmd	DELETE statement to execute; otherwise, it is constructed from the log.
upd_cmd	upd_cmd	UPDATE statement to execute; otherwise, it is constructed from the log.
filter	filter	New stored procedure to be used to filter the table (horizontal filtering). The default is NULL.
dest_table	dest_table	New destination table, if different from the source table (source_table).
dest_object	dest_object
creation_script	creation_script	Path and name of an article schema script used to create target tables. The default is NULL.
pre_creation_cmd		Pre-creation command that can drop, delete, or truncate the destination table before synchronization is applied.
	none	Does not use a command.
	drop	Drops the destination table.
	delete	Deletes the destination table.
	truncate	Truncates the destination table.
status		Specifies the new status of the property.
	include column names	Allows column names in the replicated INSERT statement.
	no column names	Allows no column names in the replicated INSERT statement.
	owner qualified	Allows owner-qualified table names.
	not owner qualified	Allows table names that are not owner-qualified.
	string literals | parameters	Whether the logreader-generated commands use the standard string_literal command format or the new parameterized command format.
schema option		Specifies the bitmap of the schema generation option for the given article. schema option is binary(8).
	0x00	Disables scripting by InitialSync and uses the provided CreationScript.
	0x01	Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on).
	0x11 (default)	The combined bitmap for generating the CREATE TABLE, creating a clustered index, and converting user-defined types to base types.
	0x10	Generates a corresponding clustered index.
	0x20	Converts user-defined data types to base data types.
	0x40	Generates corresponding nonclustered index(es).
	0x80	Includes declared referential integrity on the primary keys.
destination_owner	destination_owner	Name of the owner of the destination object.
NULL	NULL	All available property values are printed.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changearticle is used in snapshot and transactional replication.

Within an existing publication, you can change a certain article (using sp_changearticle) without having to drop and re-create the entire publication. Only unsubscribed articles can be changed.

Permissions

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

See Also