Microsoft KB Archive/827960

= You may not be able to add columns to a published article by using sp_repladdcolumn =

Article ID: 827960

Article Last Modified on 1/9/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



SYMPTOMS
When you try to add a column to a published article that participates in transactional replication, the column may not be added and you may receive the following error messages:

Server: Msg 21331, Level 16, State 1, Procedure sp_MScopyscriptfile, Line 40

Failed to copy user script file to the Distributor.(copy &quot;0&quot; &quot;< >&quot;)

Server: Msg 21285, Level 16, State 1, Procedure sp_repladdcolumn, Line 688

Failed to add column '< >' to table '< >'.

This problem may occur if the following conditions are true:
 * You try to add the column by using the sp_repladdcolumn system stored procedure in SQL Query Analyzer.
 * You do not specify the input parameters in the syntax of the sp_repladdcolumn system stored procedure.



WORKAROUND
To work around the problem, use one of the following methods:  Method 1: Add columns by using SQL Server Enterprise Manager

Add the column to the published article by using SQL Server Enterprise Manager. For more information, see the &quot;How to apply schema changes on publication databases (Enterprise Manager)&quot; topic in SQL Server 2000 Books Online.  Method 2: Provide input parameters to the sp_repladdcolumn system stored procedure as name-value pairs

To add a column to a published article by using the sp_repladdcolumn system stored procedure, provide the input parameter values as name-value pairs. For example, the following statement is the original Transact-SQL statement that is used for the sp_repladdcolumn system stored procedure: sp_repladdcolumn 'Code', 'ExtensionSupported', 'varchar(10) null', 'all', null, 0, 0 To work around the problem, you can change the original Transact-SQL statement to the following: sp_repladdcolumn @source_object = 'Code', @column = 'ExtensionSupported', @typetext = 'varchar(10) null', @publication_to_add = 'all', @schema_change_script = null, @force_invalidate_snapshot = 0, @force_reinit_subscription = 0   Method 3: Provide only the mandatory input parameters to the sp_repladdcolumn system stored procedure

If your instance of SQL Server 2000 contains only one publication, provide only the following parameters when you run the sp_repladdcolumn system stored procedure:  source_object column typetext

For example, the following statement is the original Transact-SQL statement that is used for the sp_repladdcolumn system stored procedure: sp_repladdcolumn 'Code', 'ExtensionSupported', 'varchar(10) null', 'all', null, 0, 0 To work around this problem, you can change the original Transact-SQL statement to the following: sp_repladdcolumn 'Code', 'ExtensionSupported', 'varchar(10) null' If your instance of SQL Server 2000 contains more than one publication, provide only the following parameters when you run the sp_repladdcolumn system stored procedure:  source_object</li> column</li> typetext</li> publication_to_add</li></ul>

For example, the following statement is the original Transact-SQL statement that is used for the sp_repladdcolumn system stored procedure: sp_repladdcolumn 'Code', 'ExtensionSupported', 'varchar(10) null', 'all', null, 0, 0 To work around this problem, you can change the original Transact-SQL statement to the following: sp_repladdcolumn 'Code', 'ExtensionSupported', 'varchar(10) null', 'all' </li></ul>

<div class="moreinformation_section">

Steps to Reproduce the Problem
<ol> Create a publication to set up transaction replication on your SQL Server database by using one or more tables that you want to publish.</li> Create a push subscription to the publication that you created in step 1, and then synchronize the subscription.</li>  Start SQL Query Analyzer, and then run the following Transact-SQL statement: sp_repladdcolumn '<Table_Name>', '<Column_Name>', '<Typetext>', 'all', null, 0, 0 </li></ol>

You may receive the error messages that are mentioned in the &quot;Symptoms&quot; section of this article.

<div class="references_section">