Custom Sync Objects

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
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" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> 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
|||Thanks Mary!!!

>--Original Message--
>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" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
groove
>
>.
>
|||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" <anonymous@.discussions.microsoft.com> wrote in message
news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
> 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
|||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" <hilaryk@.att.net> wrote in message
news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
> 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" <anonymous@.discussions.microsoft.com> wrote in message
> news:380f01c47ef7$7cce1900$a501280a@.phx.gbl...
>
|||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.

>--Original Message--
>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')[vbcol=seagreen]
>--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" <hilaryk@.att.net> wrote in message
>news:uOzCC55fEHA.636@.TK2MSFTNGP12.phx.gbl...
for the snapshot[vbcol=seagreen]
>which
what is in the[vbcol=seagreen]
combination of the two.[vbcol=seagreen]
the @.sync_object[vbcol=seagreen]
schema where the object[vbcol=seagreen]
the publisher you[vbcol=seagreen]
>have
in these columns as[vbcol=seagreen]
which are present[vbcol=seagreen]
>in
on this object on[vbcol=seagreen]
>the
have to trick the log[vbcol=seagreen]
often you have to[vbcol=seagreen]
tables containing[vbcol=seagreen]
procedure do a[vbcol=seagreen]
>join
message[vbcol=seagreen]
could[vbcol=seagreen]
groove
>
>.
>