Custom Repl Proc - DESPARATE HELP NEEDED

Last Post 08 Feb 2006 10:08 AM by SQL_Jr. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
SQL_Jr
New Member
New Member

--
08 Feb 2006 09:34 AM
hi. I thought I really had this resolved, but finally one test broke my replication. Here is what I need.
First there is a status col in a table that gets replicated. If col='D', it gets deleted on the subscriber periodically. Now, if the col='D', gets Updated (changed) to 'G' on the publisher, this change needs to be replicated. Remember its been deleted on the subscriber, SO, its seen as an update and calls the sp_MSupd_table proc. This of course, now must be INSERTED into the subscriber table, b/c it no longer exists. Here is my code. The top portion works fine, no issues, but if record doesn't exist it must insert.
LOGIC: If exists Do UPDATE, ELSE INSERT. (INSERT DOESN'T WORK - Q: WHERE DOES IT GET THE VALUES FROM??) PLEASE HELP ASAP!!! SOS

CREATE procedure sp_MSupd_Table
@c1 int,
@c2 int,
@c3 char(1),
@c4 varchar(255),
@c5 varchar(500),
@c6 text,
@c7 datetime,
@c8 varchar(80),
@c9 datetime,
@c10 varchar(255),
@pkc1 int,
@bitmap binary(2)
as

If exists ( select 1 from table where @c1 = @pkc1)
Begin
Update TABLE
set COL1 = Case When @c1 IS NULL THEN COL1 ELSE @c1 END,
COL2 = Case When @c2 IS NULL THEN COL2 ELSE @c2 END,
COL3 =Case When @c3 IS NULL THEN COL3 ELSE @c3 END,
COL4 = Case When @c4 IS NULL THEN COL4 ELSE @c4 END,
COL5 = Case When @c5 IS NULL THEN COL5 ELSE @c5 END,
COL6 = Case When @c6 IS NULL THEN COL6 ELSE @c6 END,
COL7 = Case When @c7 IS NULL THEN COL7 ELSE @c7 END,
COL8 = Case When @c8 IS NULL THEN COL8 ELSE @c8 END,
COL9 = Case When @c9 IS NULL THEN COL9 ELSE @c9 END,
COL10 = Case When @c10 IS NULL THEN COL10 ELSE @c10 END
where @c1 = @pkc1
and COL2 >4
End
Else
Begin
Insert TABLE(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10)
values (@pkc1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10)
End
GO
SQL_Jr
New Member
New Member

--
08 Feb 2006 10:08 AM
I guess to give a one line description of what i need is this:

HOW CAN I TURN AN UPDATE STATEMENT ON THE PUBLISHER TO AN INSERT STATEMENT ON THE SUBSCRIBER??

hope that clarifies....thx
RSP_SQL
New Member
New Member

--
08 Feb 2006 04:41 PM
I see what you're trying to do, but deleting data on the subscriber defeats the whole purpose of replication - that is to keep table data in sync. Once you manually delete it, it becomes out of synch, and the only way would be to force a snapshot to resync.

Based on your code, you want to this on a row-by-row db, i think. However, this seems to violate the basic rules of ATOMicity.

Anyway, that said, I thought about how to make it happen for you, and one suggestion is to use linked servers. If you link the publisher from the subscriber, you can use a query to insert the data from the publisher in your 'ELSE' clause.

ie Else
Begin
Insert into TABLE
Select (COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10)
from LINKEDSERVER.DB.DBO.TABLE ls
where @pkc1=ls.COL1
End

Not sure how good a solution, but only one I can think of to do what you ask. HTH


Acceptable Use Policy
---