Post reply

Urgent help with replication and data filtering

  • October 2, 2007 at 2:38 am

    #98667

    Hi,

    I have a transactional replication with 2 subscribers and I want to filter the data to one of the subscribers but I cannot do it with the row filtering options on replication because it doesn't allow join filters which are only allowed on merge replications so how can I do that with transactional?

    If you look below an example of tables I have, I am trying to publish only the data that have the COnline set to 1 on table C but want to do a join on Table D and E so that only the records on Tables C, D and E have COnline set to 1 will be published. I hope you understand what I mean.

    How can I do this? Please provide me with clear example or instructions if possible.

    Thanks so much

    I have a table C with

    CID(id) Cname(varchar) COnline(bit)

    1 Name1 1

    2 Name2 0

    3 Name3 1

    4 Name4 1

    Then I have Table D with

    DID(CID) DAge(int) Dreport(bit) Daddress(varchar)

    1 20 1 address1

    2 34 1 address2

    3 25 0 address3

    4 41 0 address4

    Then I have Table E with

    EID(CID) ESS1(text) ESS2(text) ESS3(text)

    1 a lot text a lot text a lot text

    2 a lot text a lot text a lot text

    3 a lot text a lot text a lot text

    4 a lot text a lot text a lot text

  • October 3, 2007 at 7:10 am

    #738584

    Hello,

    I've never done this. All of my transactional publications are not partitioned. There should be a way to do what you want, but I'm not sure how involved it is. It has something to do with creating DTS packages for use by the distribution agent.

    Have a look at this chunk of documentation from the msdn2 site.

    http://msdn2.microsoft.com/en-us/library/aa179200(SQL.80).aspx

  • October 3, 2007 at 12:28 pm

    #738741

    You can only have join filters on merge replications!

  • October 3, 2007 at 12:33 pm

    #738743

    Can't you write your select statement and put that in your filter ? I've done filters so that I only replicate from TableA if I get a good join to TableB.

    Here's some exact syntax from one of my filters, although I'm not using the word JOIN:

    SELECT published_columns FROM [dbo].[PROOLN_M] WHERE exists (SELECT * FROM AdvDbPrd.dbo.proord_m WHERE AdvDbPrd.dbo.prooln_m.ord_num = AdvDbPrd.dbo.proord_m.ord_num and (ordr_dte >= '1998-01-01' or ordr_dte is null))

  • October 3, 2007 at 12:36 pm

    #738746

    No you can't!

    Filters on Transactional replications only allow WHERE clause and it must have the columns from that specific table where you are setting the filter.

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

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