Alter table in a Publication

Can i alter a table included in a Publication?

I am having problems with that.

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

With this, you can alter a table and add/drop/alter a column and this DDL action will be propagated to the subscribers.

|||

For DDL opertion on replicated object tables , in SQL 2000 you can use sp_repladdcolumn/sp_repldropcolumn to add/remove the table columns

|||

Mahesh Dudgikar wrote:

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

But if my compatibility level is set to 80 - can I change it to 90, call ALTER TABLE, and then change it back to 80? Will it cause any problems?

|||

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

|||If your compatibility is set to 80 then you are running in SQL 2000 mode. So you have to use the sp_repladdcolumn/sp_repldropcolumn to add/drop columns.|||

Mahesh Dudgikar wrote:

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

1. Is compatibility level defined separately for DATABASE and for PUBLICATION?

2. If not: I have tried to change the compatibility level for unpublished database (both upgrading and downgrading), and it succeeded. Does publication lock downgrading of compatibility level of the database?

Thanks in advance!

|||

Sorry for not being clear.

Above, I meant @.publication_compatibility_level of the publication setting, not the database compat level.

They are separate settings. The database compat level has nothing to do with the DDL method (sp_repladdcolumn and alter table)

sql