Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: replication of unchanged columns - sql server 2000

    I'm trying to replicate a table from a source to a target, basic transactional replication.. both sides are SQL Server 2000.

    The problem I'm having is this. If I run an update statement at the source that updates columns A and B. But only column B actually changes, then only the change to column B will replicate to the target. I need all explicit column updates to replicate regardless of whether the value actually changes at the source or not.

    For example, at the Source, if ColA='X' and ColB='Y'
    at the target, ColA='Q', ColB='R'.
    if I run this statement at the source:
    UPDATE mytable SET ColA='X', ColB='Z'
    The result at the target is
    ColA='Q', ColB='Z'

    I've tried setting up the replication telling it both that all targets will be SQL 2000 and that all targets will be heterogenous. No luck.

    Anybody have any suggestions? Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    I've done a little more poking around on this one. I'm thinking the "problem" lies in the logreader agent.

    If I go look at the commands to be distributed with:
    distribution..sp_browsereplcmds
    ie: exec distribution..sp_browsereplcmds @article_id = XX

    I see that only the changed columns are present.
    For example, if I run the statement from before:
    UPDATE mytable SET ColA='X', ColB='Z'

    What I see in the distribution commands is:
    UPDATE mytable SET ColB='Z' WHERE...

    So the logreader is determining that no changes were made to ColA and omitting it when it posts the command to the distribution database.
    I checked the Logreader options but I didn't see anything that might help.

    The only thing I can think to try is to use an INSTEAD OF trigger to update all the columns to some preset value, then set them to the value in the INSERTED table. Pretty heavy handed though, and it will double the number of commands I am replicating.

    Anyone have any other ideas or suggestions?
    Thanks.

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Did find something else that sort of works. If I explicitly update the primary key column of the source table (even though I don't change the value), it will replicate the update as a delete-insert pair instead of an update. Not optimal though, and I hate to rely on that behavior..

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by craigmc
    For example, at the Source, if ColA='X' and ColB='Y'
    at the target, ColA='Q', ColB='R'.

    Aren't you worried about why the data is out of synch to begin with? IF for (ColA, ColB) the source is (X,Y) shouldn't the target also be (X,Y) instead of (Q,R)?

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Quote Originally Posted by buckeye234
    Aren't you worried about why the data is out of synch to begin with? IF for (ColA, ColB) the source is (X,Y) shouldn't the target also be (X,Y) instead of (Q,R)?
    This is sort of a special case, hard to explain. But basically we have data that can come from many places. IE there are many sources replicating one-way to a central consolidation server. We have many satellite offices and users radio into dispatch offices things that are entered into a database. Depending on radio coverage and area, users may switch sites they talk to. We are replicating up their status information, but we only care about the current status, ie the last one.. Could use merge replication and keep all the sites in synch on everyone, but we really don't need them to be. Each site has a small subset of users at any time so we don't want the overhead of two-way replication for everything that happens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •