SQL Server Performance

Merge Replication & Integration Services updates

Discussion in 'SQL Server 2005 Replication' started by sallemann, Sep 26, 2007.

  1. sallemann New Member

    Hi...
    I am developing an application where the corporate SQL tables are updated from external data using Integration Services (DTS) packages. Only rows that are different on the incoming external data are updated on the corporate SQL tables.
    The corporate SQL tables are then replicated out to branch offices using merge replication. But, changes that were made to the corporate tables via the DTS packages are not replicated out. If I go to the corporate tables in SSMS and type in a change to one of the published corporate tables, that data IS replicated out to the branch offices. But, our corporate data will only be changed via the DTS packages. What do I have to do to have these changes replicated out?

    Thanks for your help.
    Steph



  2. sallemann New Member

    Never mind, for now. I think it has something to do with a filter. I removed all filters on one of the tables and the DTS changes do replicate... so I'll dig into my filters to figure out what's going on.

    Thanks.
  3. satya Moderator

    Ok, but why not you take help of replication for updated data rather than using DTS?
  4. sallemann New Member

    Hi Satya,
    I have thought about that. This is why:
    -The external data is from other companies, not our own. They may or may not have SQL Server. With DTS, we can just tell them to send us a text file with the changed data.
    I am new to Replication. Now that you know why I am using DTS, do you still think Replication is a better approach than DTS?
    Thank you for your help.
    Steph
  5. satya Moderator

    Do you have constant network connectivity between these 2 sites?
  6. sallemann New Member

    No. And there will be dozens of remote sites all sending updates to the corporate data center.
  7. sallemann New Member

    I am doing some testing. The data that is updated via DTS differs from data that is changed via SSMS (where I simply type in a change to the table) in this way:
    For DTS change: MSmerge_Contents.Generation = MSMerge_Contents.PartChangeGen ... both are 71.
    For SSMS change: MSmerge_Contents.Generation (69) is greater than MSMerge_Contents.PartChangeGen (62).
    For some reason, it seems that the merge engine just doesn't 'see' the change where the PartChangeGen =71. Why? What is the PartChangeGen?
    Thanks for any help.
    Steph

  8. satya Moderator

    Do you have latest service pack on all of the SQL instances here?
  9. sallemann New Member

    I do now. I had to install a hot fix.
    Still having the same problem though.
    I'm going to go buy a book on Replication. I'm thinking it must be something about partitions (i guess).


  10. sallemann New Member

    Hmmmm... looky here... I think it might be a Microsoft bug that is addressed in a hot fix that Microsoft has not yet thoroughly tested.
    http://support.microsoft.com/kb/938363/en-us : FIX: Data is not replicated to a subscriber in a different partition by using parameterized row filters in SQL Server 2005
  11. satya Moderator

    What is the build number of this SQL instance?
  12. sallemann New Member

    Here's what I'm on right now:
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
    Mar 23 2007 16:28:52
    Copyright (c) 1988-2005 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
    I just received the hot fix... I'll install that today and see if it addresses my issue.
  13. sallemann New Member

    Well, the hot fix didn't fix it... [:(]. FYI, here's the version after applying the hot fix:
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86)
    Aug 11 2007 03:13:58
    I did find that it is one of my two filters that is causing the problem. If I remove that piece of the WHERE clause, everything comes over fine. I think it must still be a bug in SQL Server because, again, if I manually type in the change via SSMS, the change replicates... but if the same change comes in through DTS, the change doesn't replicate. [8o|]
    I'll keep plugging away at it. If I find the answer or a suitable work-around, I'll post it here.
  14. sallemann New Member

    So, I have redone my filters. My filters were calling UDFs... one of the UDFs returned a table... part of my filter's where clause was 'where vendorid not in (select udf())' (or something similar to that)... this didn't work well. When I changed my publication to include as articles the tables my udf was referencing, and then used join filters, this worked.

    I learned that you have to be careful to keep the filters pretty simple.
    Thanks for your help.
    Steph
  15. satya Moderator

    Appreciate your feedback, in fact that helps too.
    If you feel this is a bug or something then you could report the same on http://connect.microsoft.com site or if you have premier support from MS then CSS would help you anyway.

Share This Page