Filtering Columns ...Is it Possible?

• pdevadiga

  Old Hand

  Points: 358

  More actions

  May 2, 2009 at 4:14 am

  #208781

  Hi,

  This is Prashant here, I am trying to replicate a table with column filters

  using Transactional replication. I am facing huge problems in doing so.

  I would like some help regarding the same.

  Any suggestions would be welcome. Now the Problem.

  I am replicating say for example an employee table with columns

  Empid int and PK,EmpName varchar(50) & EmpRating decimal(18,2) from SERVER A to SERVER B.

  The first time I would like to replicate the entire table from SERVER A to SERVER B

  which works fine.

  But now here is the catch ...

  The EmpRating column in the Employee Table on SERVER B keeps on changing on a daily basis.

  Here I want to configure the the replication to replicate only the Empid & EmpName columns

  but not the EmpRating column.

  Is this possible? If yes then how? Where I am going wrong ?

  Any help in this matter would be highly appreciated.

  Thanx & Regards

  Prashant

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  May 2, 2009 at 6:42 am

  #987703

  Hello,

  One possible option to try would be to not use the normal Snapshot Initialisation of the Transactional Replication. Instead manually copy across the table with all the columns that you need to Server B (Please see BOL “Initializing a Transactional Subscription Without a Snapshot” for more details). The Transactional Replication Article would then only include the columns that you want updating.

  I think you also would need to be careful of the following Article Property “Action if name is in use”.

  Regards,

  John Marsh

  www.sql.lu
  SQL Server Luxembourg User Group

• ssismaddy

  SSCarpal Tunnel

  Points: 4850

  More actions

  May 3, 2009 at 7:21 pm

  #987895

  you can try replicating using scripts....in sp_addarticle you have @verticalPartition clause which allows you to filter the columns....may be you can google sp_addarticle.....

• pdevadiga

  Old Hand

  Points: 358

  More actions

  May 4, 2009 at 1:39 am

  #987959

  Hi,

  Thanx guys for your replies ...but I am comfortable with John's solution...It works like a charm...

  Only thing you need to be sure that during the manual initialize no one should be accessing the table after that it really works....On the Action property on the article I changed it to "Keep existing object unchanged". I am sure it would worked with scripts too..but the risks are too high...if some error occurs then I am done for.

  Thanx once again John....

  Regards,

  Prashant