January 7, 2003 at 9:12 am
This is an interesting one , but I'm sure someone has come across the same problem before.
We currently use transactional replication without problems.
A business requirement now wants the data to be filtered out according to a flag detrmining whether a row should be included or not in the subscriber database.
Assuming for simplicity a table of
COMPCODE,
COMPNAME,
INCLUDEFLAG
and replication is setup to filter rows of INCLUDEFLAG='Y', it works fine....BUT....
If the flag is set to 'N' after previosuly being 'Y', the subscriber needs the row to be deleted in actuality. I've considered changing the sp_MSxxxx SPs on the subscribver database to do the necessary work (check flag, delete if 'N'), but is it safe to do it or change the SPs? Would these SP changes be overwritten when the Subscriber is reinitialized?
And how would you consider solving the problem?
Ideas welcome,
Paul
January 7, 2003 at 9:19 am
Sorry, getting confused here.... replciation handles it... too many test servers, and lookign at the wrong database 🙂
January 7, 2003 at 9:20 am
I've got one of these and I realllllly hate it. Well, almost like it - we filter out pkeys beginning with certain chars, those chars are reserved for keys we create on the subscriber, then insert into both subscriber and publisher. Sometimes one or the other fails, leaving a mess.
Changing the proc is a fair solution, but you risk losing them if someone who doesn't know overwrites the procs (maybe by adding a column to the article).
The subscriber can't have the data at all, or just can't see it? If the latter I'd put up a view and be done with it!
Andy
January 15, 2003 at 6:25 am
The subscriber can't have the data at all. In testing, transactional replication does appear to handle it correctly (albeit limited testing). I have considered going down the snapshot route (which would avoid the issue), but the entire reason we're using transactional is to cut down the transfer times.
I'd be happy to leave the data in there and filter it out through views, but data can be accessed via QBE tools.
January 16, 2003 at 5:50 am
I setup a full test, and its failed.
The Distributor agent spewed out...
The row was not found at the Subscriber when applying the replicated command.
This suggests to me that a row was originally not inserted (as it didnt meet the filter criteria). The row has been changed status and now meets the criteria. However, SQLServer sees it as an Update, where it should be treated as in Insert.
I'm thinking Snapshot should be used now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply