Share via

sp_repldropcolumn (Transact-SQL)

  • Article

Drops a column from an existing table article that has been published. This stored procedure is executed at the Publisher on the publication database.

Important

This stored procedure has been deprecated and is being supported mainly for backward-compatibility. It should only be used with Microsoft SQL Server 2000 Publishers and SQL Server 2000 republishing Subscribers. This procedure should not be used on columns with data types that were introduced in SQL Server 2005 or SQL Server 2008.

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_repldropcolumn [ @source_object = ] 'source_object', [ @column = ] 'column' 
    [ , [ @from_agent = ] from_agent ] 
    [ , [ @schema_change_script = ] 'schema_change_script' ] 
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ] 
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]

Arguments

  • [ @source\_object = ] 'source_object'
    Is the name of the table article that contains the column to drop. source_object is nvarchar(258), with no default.

  • [ @column = ] 'column'
    Is the name of the column in the table to be dropped. column is sysname, with no default.

  • [ @from\_agent = ] from_agent
    If the stored procedure is being executed by a replication agent. from_agent is int, with a default of 0, where a value of 1 is used when this stored procedure is being executed by a replication agent, and in every other case the default value of 0 should be used.

  • [ @schema\_change\_script = ] 'schema_change_script'
    Specifies the name and path of a SQL Server script used to modify the system generated custom stored procedures. schema_change_script is nvarchar(4000), with a default of NULL. Replication allows user-defined custom stored procedures to replace one or more of the default procedures used in transactional replication. schema_change_script is executed after a schema change is made to a replicated table article using sp_repldropcolumn, and can be used to do one of the following:

    • If custom stored procedures are automatically regenerated, schema_change_script can be used to drop these custom stored procedures and replace them with user-defined custom stored procedures that supports the new schema.

    • If custom stored procedures are not automatically regenerated, schema_change_scriptcan be used to regenerate these stored procedures or to create user-defined custom stored procedures.

  • [ @force\_invalidate\_snapshot = ] force_invalidate_snapshot
    Enables or disables the ability to have a snapshot invalidated. force_invalidate_snapshot is a bit, with a default of 1.

    1 specifies that changes to the article may cause the snapshot to be invalid, and if that is the case, a value of 1 gives permission for the new snapshot to occur.

    0 specifies that changes to the article do not cause the snapshot to be invalid.

  • [ @force\_reinit\_subscription = ] force_reinit_subscription
    Enables or disables the ability to have the subscription reinitializated. 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.

    1 specifies that changes to the article may cause the subscription to be reinitialized, and if that is the case, a value of 1 gives permission for the subscription reinitialization to occur.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_repldropcolumn has been deprecated. Removing a column from a replicated table article should be done by executing data definition language (DDL) commands against the published table. Replication automatically replicates these DDL commands as long as DDL replication has been enabled. For more information, see Make Schema Changes on Publication Databases.

sp_repladdcolumn is still required when propagating DDL changes from republishing Subscribers running on an earlier version of Microsoft SQL Server 2000 Subscribers.

sp_repldropcolumn is used for all types of replication.

When using sp_repldropcolumn, if a schema change is made to an article that belongs to a publication that uses a Data Transformation Services (DTS) package, the schema change is not propagated to the Subscriber, and the custom procedures for INSERT/UPDATE/DELETE are not regenerated on the Subscribers. The user needs to regenerate the DTS package manually, and make the corresponding schema change at the Subscribers. If the schema update is not applied, the Distribution Agent may fail to apply subsequent modifications. Before making a schema change, make sure there are no pending transactions to be delivered.

Important

A backup of the publication database should be performed after sp_repldropcolumn has been executed. Failure to do so can cause a merge failure after a restore of the publication database.

Permissions

Only members of the sysadmin fixed server role at the Publisher or members of the db_owner or db_ddladmin fixed database roles on the publication database can execute sp_repldropcolumn.

See Also

Reference

System Stored Procedures (Transact-SQL)

Concepts

Deprecated Features in SQL Server Replication