MSSQL Replication 'Field Size Too Large'

Back from a vacation and I am ready to blog at least once a week. Last week there was an update to a large table which was involved in transactional replication. We wanted to move from using GUID as primary key and implement integer based identity key. So I had to stop publishing the table to be able to drop the primary key and add a new column, populate it create the primary key on the new column.

So we had the script executed to do the job. The I used the same script which alters the table to make the necessary changes in the subscriber database. Because the table in subscriber was in different file group with different indexes and I did not want to the replication drop and recreate the table itself (which is default). I then initiated the replication with a new snapshot and waited for 5 minutes. Then I saw the error message and found out that it's complaining about the field size too larger. How can that be? Both tables are exactly same for the basic schema. I tried to create a new snapshot with the same ugly error message. Then I decided to script out my additional indexes in subscriber and dropped the table. I created the table in subscriber from a script created in the publisher with my additional indexes in the specific file group I want. The I set the publication to create a new snapshot with leaving the target table in subscriber left as it is. And that did it.

Hope this helps.