Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for years, including frequent schema changes (new column, new constraints, etc).

The following instruction was sent on the publisher:

use myDatabase
alter table Tbl_TypeLignePaye
      alter column natureTypeLignePaye nvarchar(12)
go

where field natureTypeLignePaye was originally nvarchar(3) null

The query ran without errors on the main database. The result is the following:

  1. The field natureTypeLignePaye still appears as nvarchar(3) in the object browser
  2. The column properties screen indicates a nvarchar type with a length of 12
  3. The change was not propagated to the subscribers

Any idea on what is going on with this database?

Publisher: object browser window vs property window give incoherent data

field type and length

share|improve this question
    
See my answer dba.stackexchange.com/a/45444/8783 that will help you. –  Kin Jun 29 '13 at 20:04
    
Did you see my answer ? –  Kin Aug 28 '13 at 17:42

2 Answers 2

Well I am not saying we know why this happened, but DDL instruction was properly distributed once we stopped\restarted the SQL service on the publisher ...

share|improve this answer
    
I'm pretty sure DDL will only come over with the new snapshot –  swasheck Jun 29 '13 at 17:15
    
Yes in Pub/Sub replication DDL will only come over with the new snapshot. So whenever you work at DDL you have to pulish to propagate. –  JP Chauhan Jul 30 '13 at 7:31
2  
sorry but adding. modifying and deleting columns and constraints do not need a new snapshot. Adding or deleting a table would require such a step. –  Philippe Grondier Aug 2 '13 at 13:20

If anyone wants to increase the column width of the replicated table in SQL Server 2008, then no need to change the property of replicate_ddl=1. Simply follow the steps as given.

  1. Open SSMS
  2. Connect to Publisher database
  3. run command

    ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22)
    
  4. It will increase the column width from varchar(x) to varchar(22) and same change you can see on subscriber (transaction got replicated). So no need to re-initialize the replication

Hope this will help all who are looking for it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.