BUG: Noncontiguous Colids in Syscolumns Causes Sp_addarticle to Fail with Error 50007 (281093)

SYMPTOMS

When you are adding an article to a publication with an immediate-updating subscriber, you may get the following error if certain conditions are met:

Server: Msg 50007, Level 16, State 1, Line 0
Disallowed implicit conversion from data type [datatype] to data type
timestamp, table [tablename], column 'msrepl_synctran_ts'. Use the
CONVERT function to run this query.
Server: Msg 50007, Level 16, State 1, Line 0
xp_execresultset: unable to execute result set
Server: Msg 50007, Level 16, State 1, Line 0
Disallowed implicit conversion from data type [datatype] to data type
CONVERT function to run this query.
Server: Msg 50007, Level 16, State 1, Line 0
xp_execresultset: unable to execute result set
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'dbo.sp_MSsync_upd_test_2'.

CAUSE

Every column in every table has a column ID (colid) number. This error is caused by noncontiguous column IDs in the publishing table. If the colid numbers are not in a consecutive sequence, then you have noncontiguous colids. This may have occurred if you originally created the table with more columns and then deleted some of the columns.

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 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 attempt to insert a row on the publisher with values in columns c1, c2, and c5, you will receive the error message described in the "Symptoms" section of this article.

WORKAROUND

To work around this problem, you can drop the existing publishing table with noncontiguous colids, and then recreate 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 recreated 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.
Make sure the Select Into/Bulk Copy option is enabled in the publishing database. To check this, do the following in Enterprise Manager:
1. Right-click the database name and click Properties on the shortcut menu.
2. On the Options tab, make sure the Select Into/Bulk Copy check box is selected.
On the publisher, execute the following statement:
```
SELECT * INTO Table2 
FROM Table1
						
```
This copies all the data into a new table. All the columns will have sequential colids. 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 recreated.
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 as Table1).
Recreate any dependent objects that you had scripted in step 1 of this procedure.
Recreate the publication.

MORE INFORMATION

You can determine whether or not you have noncontiguous columns in your table by looking at the colid number sequence for the table. Execute the following statement, replacing "TABLE1" with the name of your table:

use your_database_name
SELECT name, colid 
FROM syscolumns 
WHERE id = object_id('TABLE1') order by colid