1

I'm running SQL Server 2008 Std with a database that is being published in a "Transactional Publication" to a single subscriber.

We are unable to make any changes to the tables on the publisher without getting the "cannot modify table because it is published for replication". This seems odd because schema changes (or scripts run to do this) should be pushed to the subscriber. We currently have to drop the entire publication system to make table changes.

What am I missing? There must be a way to update the publisher tables?

thanks!

typemismatch
  • 205
  • 3
  • 9

1 Answers1

1

The table modification that you are trying to do was most probably done in the graphical table designer in SSMS. The implementation of table changes through the graphical designer is poor.

When you save the changes, SSMS runs off and copies the entire table into a new table with the new design incorporated, then drops the 'old' table and renames the new one to replace it.

Be aware that column ordering is of little consequence for tables stored in SQL Server, there is no need to add new columns in the 'middle' of a table other than it may look nicer :=)

As your table is in a transactional replication, SSMS cannot perform the background drop table statement and you get the error message you noted.

To make the changes that you want (add or drop a column) you need to perform the commands in T-SQL

ALTER TABLE dbo.Table ADD NewCol int NULL;
ALTER TABLE dbo.Table DROP COLUMN NewCol;

Please note that the command syntax is inconcistent (thank you MS!). Adding a column does not need/allow the keyword COLUMN to be specified, where dropping requires it.

As long as your replication is correctly setup to replicate schema changes, these commands will work and be propogated to the subscriber.

Changes to a table can be made this way, with the exception of changing the primary key. The PK is required for transactional replication to work and cannot be changed. You would need to remove the article from replication if you wanted to change these columns.

Hope that helps.

sql_williamd
  • 111
  • 1