Exclude column from replication, but leave column on subscriber.

It seems I can't figure out how to have two tables, say integer columns named
A, B, and C, with a rowguid column.
I want that table structure on my subscriber (A, B, C, rowguid) but I only want
to replicate data from columns A and B.
As it stands, replication tries to create it on the subscriber as A, B,
rowguid, leaving the C column off.
Thanks for any help.
Brian K
should have specified, this is merge replication, SQL Server 2000.
Brian
In article <4IJtc.16319051$Id.2708018@.news.easynews.com>, Brian Keener wrote:
> It seems I can't figure out how to have two tables, say integer columns named
> A, B, and C, with a rowguid column.
> I want that table structure on my subscriber (A, B, C, rowguid) but I only want
> to replicate data from columns A and B.
> As it stands, replication tries to create it on the subscriber as A, B,
> rowguid, leaving the C column off.
> Thanks for any help.
> Brian K
|||Brian,
is it a requirement that the rowguid changes independantly on publisher and
subscriber? If not and the guid can't change then why not include it?
Regards,
Paul Ibison
|||In article <OT6YKXNREHA.3300@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> is it a requirement that the rowguid changes independantly on publisher and
> subscriber? If not and the guid can't change then why not include it?
> Regards,
> Paul Ibison
>
Hmm, rowguid is not a column I'm worried about. It can stay the same
across the board. Row C need to be able to change independently on
the two tables.
Brian K
|||Brian,
you'd have to edit the merge triggers to enable this by preventing entries
into MSmerge_contents from changes to C, and I wouldn't recommend it. It
would also make validation impossible. You could however achieve the same
functionality using views - the view would be a join returning columns A, B,
C, rowguid while the underlying tables would be
table1: A,B,rowguid and
table2: C,pk (one of A or B)
Then you just replicate table1. If changes can occur to cols A/B and C in
one update then you'll need to set up instead of triggers.
HTH,
Paul Ibison
|||its not the merge triggers you edit for this. Its the stored procedures that
is uses which look like this
sp_ins_3477CF08A8A2481D3269F4FE171245B8
look for a statement that looks like this.
if @.retcode<>0 or @.@.ERROR<>0
begin
set @.errcode= 0
goto Failure
end
insert into [dbo].[authors] ( [au_id] , [au_lname] , [au_fname] , [phone] ,
[address] , [city] , [state] , [zip] , [contract] , [msrepl_tran_version] ,
[rowguid] ) values ( @.p1 , @.p2 , @.p3 , @.p4 , @.p5 , @.p6 , @.p7 , @.p8 , @.p9 ,
@.p10 , @.p11 )
select @.rowcount= @.@.rowcount, @.error= @.@.error
do this for both the insert and update procedures
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23zbYbaOREHA.3012@.tk2msftngp13.phx.gbl...
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A,
B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
|||Hilary,
I agree that it could be done in the stored procedure, but think the trigger
modification is simpler:
For an update, what I had in mind is something like
IF Not UPDATE(C)
BEGIN
existing trigger code
END
The insert and delete triggers could be left as they are, as both should
propagate through the normal merge replication.
Cheers,
Paul Ibison
|||Paul
After posting my solution I had reservations about it.
The advantage of your solution is the update trigger will fire but won't
write replication metadata for updates to the columns you wish to exclude.
My solution is that all updates will be propagated to the subscriber, but
will not be applied.
In retrospect I think your solution is perhaps the better one.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23c9lNXaREHA.2408@.tk2msftngp13.phx.gbl...
> Hilary,
> I agree that it could be done in the stored procedure, but think the
trigger
> modification is simpler:
> For an update, what I had in mind is something like
> IF Not UPDATE(C)
> BEGIN
> existing trigger code
> END
> The insert and delete triggers could be left as they are, as both should
> propagate through the normal merge replication.
> Cheers,
> Paul Ibison
>
|||In article <#zbYbaOREHA.3012@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A, B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
Pretty much what I assumed.
I've already implemented this as a view in our test environment, but
thanks for the information.
Brian K