Adding a column to a replicated table in SQL Server 2005

A problem that I have come across a number of times, yet seem to forget how to solve, is when you want to add a column to a table that you have set up for replication in SQL Server 2005.

SSMS (SQL Server Management Studio) modifies a table by simply dropping it, then re-creating it again with the new definition. However, when a table is replicated you’re not allowed to drop it (all kinds of nasty things could happen). Of course SSMS doesn’t tell you this is the problem, it just complains of something similar to this:

“Error validating the default for column ‘rowguid’."

The only way (that I’ve found) round this, is to manually alter the table. Something I got taught at University, however I don’t use it much in practice so had to look it up. Luckily the syntax is nice and simple:

ALTER TABLE [table] ADD [column] nvarchar(50) NOT NULL DEFAULT ('') GO

This will add a column with the name ‘column’ to the table ‘table’ that is an nvarchar, with a max size of 50, that isn’t allowed to be NULL, and has a default value of an empty string.

Hopefully this will help someone, at the very least, it should give me somewhere to come back to if/when I run into this issue again.

Replication is great in theory, unfortunately, it is one of the most troublesome areas I’ve had to deal with.