what are the correct steps to alter a PK column of a table that is a merge replication source in SQL SVR 2005?

Question

I have a table that has an identity column as the Primary Key. None of the data in the table is important at this point so I can delete it all, but I want to change the column from int to bigint and possibly reseed it to bigint.minvalue so as to give the largest possible range before running out of values. I would like to do this without having to rebuild the replication or having to modify the subscribers.

If I execute

alter table MyTable
 alter column MyTableId bigint not null

I get the errors:

Msg 5074, Level 16, State 1, Line 5
The object 'repl_identity_range_CEEB13F5_11D2_435C_BE5D_EBF91EBF8DE7' is dependent on column 'MyTableId'.
Msg 5074, Level 16, State 1, Line 5
The object 'PK_MyTable' is dependent on column 'MyTableId'.
Msg 4922, Level 16, State 9, Line 5
ALTER TABLE ALTER COLUMN MyTableId failed because one or more objects access this column.

Answer 1

You cant change a primary key on a published table as its part of the system used to push the data around ; so remove the table from the publication; make the changes and republish it.

You can drop your table using sp_droparticle http://msdn.microsoft.com/en-us/library/ms173832.aspx

Then make your table / your key changes

Finally use sp_addmergearticle to add it back in http://msdn.microsoft.com/en-us/library/ms174329.aspx