Post reply

Don't want to replicate certain update t-sql

  • April 7, 2010 at 9:13 am

    #220280

    Hi,

    I'm trying to build a historical database as a copy of an existing SQL2005 database and i'm considering a SQL2005 replication solution. My main problem is that i want to replicate everything from database#1 to database#2, EXCEPT a certain update t-sql which is a pruning job (sets soundata = NULL). All other changes must be replicated.

    Database#1 should only have sounddata 30days back (pruning job fixes this).

    Database#2 should have all sounddata.

    I can't use the replicationfilter on the article properties i guess. That filters all the updates or is there a way to choose only a certain update-SQL? If I turn of the replication before the pruning job on database#1 then i'll get the (update ...set sounddata=NULL) the next replication and this won't work.

    In my understanding, the only way a sound is "deleted" is when the pruning job updates it to NULL.

    Any ideas?

    Maybe another solution to this?

    Thankful for any suggestions here

    //Leo

  • April 7, 2010 at 10:25 am

    #1147532

    You will have to write custom replication stored procs, specifically the sp_MSupd_<articlename> procedure. Replication allows to create and use custom code in the article's change propagating stored procedures, namely=INS_CMD, UPD_CMD and DEL_CMD parameters of sp_addarticle.

    You can write you code of checking if the sounddata column is being set to NULL and then skip that change inside this custom stored proc that you are writing.

    check: http://msdn.microsoft.com/en-us/library/ms152489.aspx for more details on this.

  • April 8, 2010 at 12:21 am

    #1148045

    Thank you very much, I'll check out the sp_MSupd proc then 🙂

  • April 8, 2010 at 8:05 am

    #1148292

    You're welcome!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply