Filtering a column on an article without reinitializing subscribers

• Chrislis

  SSChasing Mays

  Points: 622

  More actions

  October 26, 2005 at 8:10 am

  #88334

  I wan't to restrict a column from being replicated without reinitializing subscribers. (without using Enterprise Manager - Filter Column).

  From my knowledge, the only way to do this is to update the system tables syscolumns and sysarticles on the publisher database. I had some tentatives but it didn't work as I expected.

  Before this, I successfully dropped an article from a publication without reinitializing subscribers using this method.

  For dropping an article I used this script:

  -----------------------------------------------------------

  delete from sysarticles where name = 'xXx'

  sp_configure 'allow updates', 1

  go

  reconfigure with override

  go

  update sysobjects set replinfo = 0 where id = 1842105603

  exec dbo.sp_replupdateschema xXx

  update syscolumns

  set colstat = 0

  from syscolumns sc

  inner join sysobjects so on so.id = sc.id

  where so.id = 1842105603

  exec dbo.sp_replupdateschema xXx

  sp_configure 'allow updates', 0

  go

  reconfigure with override

  go

  delete from distribution.dbo.msarticles where publication_id = 2 and article_id = 174

  delete from distribution.dbo.MSsubscriptions where publication_id = 2 and article_id = 174

  -------------------

  Any sugestions please?

• October 31, 2005 at 8:00 am

  This was removed by the editor as SPAM

• Chris Becker

  SSCrazy

  Points: 2585

  More actions

  October 31, 2005 at 11:46 am

  #601391

  Never update system tables. Drop article from pub, then re-add specifying columns...

  exec sp_dropsubscription @publication =  'MyPubName'

      ,  @article =  'MyTable'

      , @subscriber =  'SERVERNAME'

  GO

  exec sp_droparticle @publication = 'MyPubName'

      , @article = 'MyTable'

  GO

  exec sp_addarticle @publication = N'MyPubName', @article = N'MyTable', @source_owner = N'dbo' ...

  GO

  -- Adding the article's partition column(s)

  exec sp_articlecolumn @publication = N'MyPubName', @article = N'MyTable', @column = N'Field1'

  , @operation = N'add'

  GO

  exec sp_articlecolumn @publication = N'MyPubName', @article = N'MyTable', @column = N'Field2'

  , @operation = N'add'

  GO

  exec sp_articlecolumn @publication = N'MyPubName', @article = N'MyTable', @column = N'Field3'

  , @operation = N'add'

  GO

  exec sp_addsubscription @publication = 'MyPubName'

      , @article = 'MyTable'

      , @subscriber = 'SERVERNAME'

      , @destination_db = 'SubscrDB'

      , @sync_type = 'none'   -- this does not initialize table, change to 'automatic' to init

  GO

  Regards,

  ChrisB MCDBA OCP MyDatabaseAdmin.com

  Chris Becker bcsdata.net