Rename a NOT NULL column with no default in a SQL Server 2000 table in Merge Replication

views:

answers:

Rename a NOT NULL column with no default in a SQL Server 2000 table in Merge Replication

Using sp_repladdcolumn I can either:
1) add a column that allows nulls; or
2) add a column with a default value that does not allow nulls.

Without dropping and recreating the whole subscription I would like to:
3) add a column with no default value that does not allow nulls.

Is this possible?

I realize I would need to populate the column with data before I could set it to NOT NULL. As the title indicates, what I really want to do is rename an existing column that: a) has data in it, b) is set to NOT NULL, and c) has no default value.

To just rename a column, why not just use sp_rename?

Chris Shaffer 2009-11-24 22:25:04

Fails with this error:Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172Cannot rename the table because it is published for replication.

mwolfe02 2009-11-24 22:31:48

Sorry - I would downvote myself for not reading the question closely enough to notice the parts about replication if I could. I don't know too much about replication, but on a standard table if I wanted to add a new non-nullable column I would add it with a default then drop the default constraint... Is that an option?

Chris Shaffer 2009-11-25 16:46:31

There does not appear to be any way to alter a column (to include dropping the default constraint) without turning off the merge replication, making the alteration, then turning it back on. Unfortunately, turning the replication off and on is not trivial. It can be scripted easily enough but it takes several hours to regenerate the snapshot and all of that work must be done when the users are not around (i.e., night/weekend) which I'd like to avoid if possible. I can enforce the NOT NULL through the front-end, but I would prefer to have the database engine handle that itself.

mwolfe02 2009-11-27 14:23:07

ansaurus

tags:

views:

answers:

Rename a NOT NULL column with no default in a SQL Server 2000 table in Merge Replication

related questions