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.

Advertisements

One Response to “Adding a column to a replicated table in SQL Server 2005”

  1. Thank u very much

    I have run above query at publisher Database and get what i want.

    Thanks once again

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: