1

We have a database a that is replicated to a subscriber db b (used for SSRS reporting) every night at 2.45 AM.

We need to add a column to one of the replicated tables since it's source file in our iSeries is having a column added that we need to use in our SSRS reporting db.

I understand (from Making Schema Changes on Publication Databases) and the answer here from Damien_The_Unbeliever) that there is a default setting in SQL Server Replication whereby if we use a T-SQL ALTER TABLE DDL statement to add the new column to our table BUPF in the PION database, the change will automatically propagate to the subscriber db.

How can I check the replication of schema changes setting to ensure that we will have no issues with the replication following making the change?

Or should I just run ALTER TABLE BUPF ADD Column BUPCAT Char(5) NULL?

Community
  • 1
  • 1
Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148

1 Answers1

2

To add a new column to a table and include it in an existing publication, you'll need to use ALTER TABLE < Table > ADD < Column > syntax at the publisher. By default the schema change will be propagated to subscribers, publication property @replicate_ddl must be set to true.

You can verify if @replicate_ddl is set to true by executing sp_helppublication and inspecting the @replicate_ddl value. Likewise, you can set @replicate_ddl to true by using sp_changepublication.

See Making Schema Changes on Publication Databases for more information.

Brandon Williams
  • 3,695
  • 16
  • 19
  • we found that we need to disable replication on the published table as otherwise we get a replication error message about `sqltrans` or something – Our Man in Bananas Apr 23 '15 at 16:51
  • You shouldn't be getting an error and you shouldn't need to disable replication to make a schema change. What is the exact error you are receiving and what syntax are you using to make the schema change? – Brandon Williams Apr 23 '15 at 17:28
  • I think it was something about `sp_MStran_altertable` and we are using `ALTERTABLE BUPF ADD BUPCAT Char (5) NULL` ... – Our Man in Bananas Apr 23 '15 at 20:07