Custom Sync Objects

[ChrisR]

sql2k sp3

Can someone point out to me the steps to take to use
Custom Sync Objects? My Publisher and Subscriber have
different schemas which is the reason for they're use.
Obviously I need to take into account the Insert, and
Update Procs, as well as the Subscriber schema. I could
see this taking quite a while until I get into the groove
of how this all works.

TIA, ChrisR

[Mary Bray]

Hi chris,
I have a slightly similar issue where I wanted to change the ins stored
procs for a number of the tables on the subscriber. I ended up putting all
my alter proc commands in a PostSnapshot script and specifying that in the
sp_addpublication procedure. This runs after the snapshot so all the data
should be in the tables and you could run alter table.

"ChrisR" <anon...@discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501...@phx.gbl...

[ChrisR]

Thanks Mary!!!

>.
>

[Hilary Cotter]

You use custom sync objects when you want to bcp data for the snapshot which
will be sent to the Subscriber.

The custom sync object can send different data than what is in the
underlying object, or different schema, or a combination of the two.

To get this to work you need to

1) create the custom sync object - a view
2) tell SQL Server to use this custom sync object with the @sync_object
parameter of sp_addarticle.

If you are replicating to a table with a different schema where the object
on the subscriber has more columns than the object on the publisher you have
to ask yourself this question. How am I going to fill in these columns as
the log reader will only fill in values for the columns which are present in
the underlying object on the Publisher?

If you are putting defaults on the additional columns on this object on the
Subscriber, its not a problem, but if you aren't you have to trick the log
reader into reading these additional columns. Most often you have to
replicate from a different source object, or replicate tables containing
these additional values, and then in your custom stored procedure do a join
to fill them in.

Here is an example:

CREATE VIEW Authors_View

AS

SELECT authors.au_id, titles.title_id, au_lname,

au_fname, phone, address, state, zip, contract, city,

title

FROM authors, titles, titleauthor

WHERE authors.au_id = titleauthor.au_id

AND titles.title_id = titleauthor.title_id

GO

sp_addarticle @publication = 'test',

@article = 'AuthorTitle',

@source_object = 'AuthorTitle',

@destination_table = 'AuthorTitle',

-Specifying that we are using a custom sync object

@type = 'logbased manualview',

-Our sync object

@sync_object='Authors_View',

-Our custom script

@creation_script = 'c:\temp\AuthorTitle.sql',

@pre_creation_cmd = 'delete',

-Specifying we want to auto generate our stored

procedures

@schema_option = 0x02,

@status = 8,

@ins_cmd = 'CALL sp_MSins_AuthorTitle',

@del_cmd = 'CALL sp_MSdel_AuthorTitle ',

@upd_cmd = 'MCALL sp_MSupd_AuthorTitle '

GO

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

"ChrisR" <anon...@discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501...@phx.gbl...

[Hilary Cotter]

That example wasn't the best - try this one.

create database CustomSyncObject
GO
create database CustomSyncObjectSub
GO
use CustomSyncObject
go
sp_dboption 'CustomSyncObject','published', 'true'
go
create table test(pk int not null primary key identity(1,1),
charcol1 char(20),
charcol2 char(20),
charcol3 char(20))
go
declare @counter int
set @counter=0
while @counter<100
begin
insert into test (charcol1, charcol2, charcol3) values(getdate(),
System_user,@counter)
select @counter=@counter+1
end
create View TestView
as
select charcol1, charcol2, charcol4=convert(char(20),'') From test
GO

sp_addpublication 'Custom', @status='active'
GO
sp_addpublication_snapshot 'custom'
GO
sp_addarticle @publication = 'Custom',
@article = 'Custom',
@source_object = 'test',
@destination_table = 'TestWithDifferentSchema',

@type = 'logbased manualview',

@sync_object='TestView',
@creation_script = 'c:\temp\TestWithDifferentSchema.sql',
@pre_creation_cmd = 'delete',
@schema_option = 0x0,
@status = 8,
@ins_cmd = 'CALL sp_MSins_CustomProc',
@del_cmd = 'CALL sp_MSdel_CustomProc',
@upd_cmd = 'MCALL sp_MSupd_CustomProc'
GO
sp_addsubscription 'Custom', 'Custom', @@Servername, 'CustomSyncObjectSub'
GO
Use CustomSyncObjectSub
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_CustomProc') drop proc [sp_MSins_CustomProc]
go
create procedure [sp_MSins_CustomProc] @c1 int,@c2 char(20),@c3 char(20),@c4
char(20)

AS
BEGIN

insert into [TestWithDifferentSchema](
[pk], [charcol1], [charcol2]
)

values (
@c1, @c2, @c3
)

END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_CustomProc') drop proc [sp_MSdel_CustomProc]
go
create procedure [sp_MSdel_CustomProc] @pkc1 int
as
delete [TestWithDifferentSchema]
where [pk] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
GO
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_CustomProc') drop proc [sp_MSupd_CustomProc]
go
create procedure [sp_MSupd_CustomProc]
@c1 int,@c2 char(20),@c3 char(20),@c4 char(20),@pkc1 int
as
if @c1 = @pkc1
begin
update [TestWithDifferentSchema] set [charcol1] = @c2,[charcol2] = @c3
where [pk] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [test] set [pk] = @c1,[charcol1] = @c2,[charcol2] = @c3
where [pk] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end

GO
Use CustomSyncObject
sp_repladdcolumn 'test','chrisRSpecial','char(20)'
insert into test (charcol1, charcol2,charcol3) values('test','test','test')
--before reinitializing we have to
--update custom sync object for newly added columns
--update replication stored procedurs

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

"Hilary Cotter" <hil...@att.net> wrote in message
news:uOzCC55...@TK2MSFTNGP12.phx.gbl...

[ChrisR]

Thank you so much. As it turns out Ive been putting out a
day long fire and havent even tried any of it. Theres
always tomorrow. Thanks again.

>.
>