Article ID: 241658
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q241658
BUG #: 56005 (SQLBUG_70)
SYMPTOMS
After inserting a row into a replicated table on the publisher, the distribution task may receive the following error message:
When you look at the destination table on the subscriber, you will notice that one or more columns are missing from the table.
CAUSE
Every column in every table has a column ID (colid) number. This error is caused by non-contiguous column IDs in the publishing table. If the colid numbers are not in a consecutive sequence, then you have non-contiguous colids. This could have been caused if you had originally created the table with more columns and some of the columns were dropped.
For example, if you create a table with columns c1-c5, they will have colid values of 1-5. If you then use an ALTER TABLE statement to drop columns c3 and c4, the remaining colid sequence will be 1, 2, 5. If you have non-contiguous colid values for the columns of a table, only the columns with contiguous colid values will be replicated. In this example, only columns c1 and c2 would appear on the subscriber. If you then attempted to insert a row on the publisher with values in columns c1, c2, and c5, you would receive the error message described in the SYMPTOMS section of this article.
WORKAROUND
To work around this problem, you should drop the existing publishing table with non-contiguous colids, then re-create it so that the colids are contiguous. To do this, perform the following steps (note that this example uses table names "Table1" and "Table2"; you should replace these with your own table names):
- Make sure you script any indexes, constraints, defaults, rules, triggers, and other objects so that they can be re-created after the table is dropped. Also, make a note of any dependencies on this table; you can run the sp_depends stored procedure to get a list of these.
- Delete the subscription and publication on the publisher. Drop the table from the subscription database on the subscriber.
- Make sure the Select Into/Bulk Copy option is enabled in the publishing database. To check this, do the following in Enterprise Manager:
- Right-click the database name and click Properties on the shortcut menu.
- On the Options tab, make sure there is a check in the Select Into/Bulk Copy check box.
On the publisher, execute the following statement:
SELECT * INTO Table2 FROM Table1This copies all the data into a new table. Also, all the columns will have a sequential colid. However, any indexes, triggers, constraints, and so on are not copied. This is why it is important to have these objects scripted, so that they can be re-created.
- Drop the original table (that is, Table1) on the publisher.
- Use the sp_rename stored procedure to rename the new table as the original table (for this example, rename Table2 to Table1).
- Re-create any dependent objects that you had scripted in Step 1 of this procedure.
- Re-create the publication and subscription.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
MORE INFORMATION
You can determine whether or not you have non-contiguous columns in your table by looking at the column ID (colid) number sequence for the table. Execute the following statement, replacing "TABLE1" with the name of your table:
SELECT name, colid
FROM syscolumns
WHERE id = object_id('TABLE1')
Additional query words: repl col cols
Keywords: kbbug kbfix KB241658
