Export (0) Print
Expand All
Expand Minimize

sp_changearticle

SQL Server 2000

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

Syntax

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

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 properties of articles and the values for those properties.

Property Values Description
description   New descriptive entry for the publication job.
sync_object   Name of the table or view used to produce a synchronization output file. The default is NULL.
type 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.
Article type.
ins_cmd   INSERT statement to execute; otherwise, it is constructed from the log.
del_cmd   DELETE statement to execute; otherwise, it is constructed from the log.
upd_cmd   UPDATE statement to execute; otherwise, it is constructed from the log.
filter   New stored procedure to be used to filter the table (horizontal filtering). The default is NULL.
dest_table   New destination table.
dest_object   Provided for backward compatibility. Use dest_table.
creation_script   Path and name of an article schema script used to create target tables. The default is NULL.
pre_creation_cmd Precreation 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 Specifies 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). If this value is NULL, the system will auto-generate a valid schema option for the article. The table given in the Remarks shows the value that will be chosen based upon the combination of the article type and the replication type. Also, not all schema_option values are valid for every type of replication and article type. The Valid Schema Option table given in the Remarks shows the valid schema options that can be chosen, based upon the combination of the article type and the replication type.
0x00 Disables scripting by InitialSync and uses the provided CreationScript.
0x01 Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on).
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.
  0x100 Replicates user triggers on a table article, if defined.
  0x200 Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table will not be replicated.
  0x400 Replicates check constraints.
  0x800 Replicates defaults.
  0x1000 Replicates column-level collation.
  0x2000 Replicates extended properties associated with the published article source object.
  0x4000 Replicates unique keys if defined on a table article.
  0x8000 Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements.
destination_owner destination_owner Name of the owner of the destination object.
NULL NULL  

[@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 will not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error will occur and no changes will be 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.

[@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 will not cause the subscription to be reinitialized. If the stored procedure detects that the change would require existing subscriptions to be reinitialized, an error will occur and no changes will be made. 1 specifies that changes to the article will cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changearticle is used in snapshot replication and transactional replication.

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

The table describes the default @schema_option value that will be chosen for the stored procedure if a NULL value is passed in by the user. The default value is based upon the replication type shown across the top, and the article type shown down the first column.  Empty cells are article type and replication types that are not valid pairs, and therefore, would have no default.

Article Type Replication Type
  Transactional Snapshot
logbased 0xF3 0x71
logbased manualfilter 0xF3 0x71
logbased manualview 0xF3 0x71
indexed view logbased 0xF3 0x71
indexed view logbased manualfilter 0xF3 0x71
indexed view logbased manualview 0xF3 0x71
indexed view logbase manualboth 0xF3 0x71
proc exec 0x01 0x01
serialized proc exec 0x01 0x01
proc schema only 0x01 0x01
view schema only 0x01 0x01
func schema only 0x01 0x01
indexed view schema only 0x01 0x01
table    

Note  If a publication is enabled for queued updating, the @schema_option values of 0x8000 and 0x0080 will be added to the default value shown in the table.

Valid Schema Option Table

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 and 0x2000 0x01 and 0x2000
serialized proc exec 0x01 and 0x2000 0x01 and 0x2000
proc schema only 0x01 and 0x2000 0x01 and 0x2000
view schema only 0x01, 0x0100, and 0x2000 0x01, 0x0100, and 0x2000
func schema only 0x01 and 0x2000 0x01 and 0x2000
indexed view schema only 0x01, 0x10, 0x040, 0x0100, and 0x2000 0x01, 0x10, 0x040, 0x0100, and 0x2000
table    

Note  For queued updating publications, the @schema_option values of 0x8000 and 0x80 must be enabled.

Permissions

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

See Also

sp_addarticle

sp_addpublication

sp_articlecolumn

sp_changepublication

sp_droparticle

sp_droppublication

sp_enumfullsubscribers

sp_helparticle

sp_helparticlecolumns

sp_helppublication

System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft