DDL Still Replicates Even With DDL Replication Off (@replicate_ddl = 0).

There are times when we need to make DDL changes and have them NOT replicate to the subscription database. The documented approach is to disable DDL replication using sp_changepublication (replicate_ddl = 0). See http://msdn2.microsoft.com/en-us/ms147331.aspx

However, our DDL still replicates even when following those steps. I've even tried the very simple steps below to reproduce the error.

Set up transactional replication of one small test table. Replication is working fine. Set @.replicate_ddl = 0 with sp_changepublication. I manually verify the setting (using the GUI) before continuing. Run a simple alter on the publication DB; change one column from varchar(25) to varchar(30). "ALTER TABLE <tablename> ALTER COLUMN <colname> VARCHAR(30) NULL". The DDL still replicates. The subscription table still has the column altered to the new definition.

I've even tried manually setting the option in the GUI and also stopping and restarting the synchronization after changing the setting; nothing works.

We are using SQL Server 2005 and just upgraded to SP2 a few days ago.

I have not found any other articles/postings on the topic so it appears we are the only ones having this problem. If anyone has any advice or input, it is greatly appreciated.

Regards,

Adam

Adam;

This turns out to be a problem with MS SQL Server Management Studio. When you upgraded your SQL Server 2005 to SP2 you did not upgrade your local copy of MS SQL Server Management Studio to SP2.

To fix this upgrade your local MS SQL Server Management Studio to SP2 and recreate your replication.

HTH

Paul