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
>
>.
>

Custom Resolver in SQL Server 2005

I'm trying to implement .NET based custom resolver for SQL Server 2005. MS
example shows how to work with publisher / subscriber record data only. To
resolve a conflict I need more info about the business object represented by
record data. I need to run a query / procedure for this purpose. To make a
connection from within my resolver user / password are required. No such
thing is available in 'Microsoft.SqlServer.Replication.BusinessLogicSupp ort'
class.
Note: In SQL Server 2000 VB based COM custom resolver I have
'IReplRowChange' object as an INPUT param for 'IVBCustomResolver_Reconcile'
method which has 'GetSourceConnectionInfo' / 'GetDestinationConnectionInfo'
methods to get to GetLogin / GetPassword and than use ADODB to create a
connection string to run query or sp.
Appreciate any help on this subject.
Is there any workaround to use Custom Resolver COM created for SQL Server
2000 implementing IVBCustomResolver for SQL Server 2005?
|||I've found an example in
C:\Program Files\Microsoft SQL
Server\90\Samples\Replication\Merge\BusinessLogic\ CS
Thanks All

Custom Procedure Replication Error with Oracle Subscriber

The custom procedure was created successfully on Oracle.
The following is the error message that is recieved. What is really odd is
why SQL Server is sending a select of a stored procedure name? Any ideas
as to what is going on here?
SQL> select * from sp_upd_crms_repl where 0 = 1;
select * from sp_upd_crms_repl where 0 = 1
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
Oracle publishers only replicate tables. It looks like here Oracle is
treating the stored procedure as a table, and is trying to return all the
data as opposed to only the columns (where 1=1).
Oracle publications do add the following objects on the Oracle server:
http://msdn2.microsoft.com/en-us/library/ms152557.aspx
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
news:uoQ%232bjwHHA.4800@.TK2MSFTNGP05.phx.gbl...
> The custom procedure was created successfully on Oracle.
> The following is the error message that is recieved. What is really odd
> is why SQL Server is sending a select of a stored procedure name? Any
> ideas as to what is going on here?
> SQL> select * from sp_upd_crms_repl where 0 = 1;
> select * from sp_upd_crms_repl where 0 = 1
> *
> ERROR at line 1:
> ORA-04044: procedure, function, package, or type is not allowed here
>
>
|||The Oracle side is actually the subscriber. It does appear that it is
treating this as a table instead of a stored procedure. I can't see
anything wrong with the article. The article has the insert and update
commands defined as CALL stored procedures and the actual command names are
the correct stored procedure names.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIyD82kwHHA.600@.TK2MSFTNGP05.phx.gbl...
> Oracle publishers only replicate tables. It looks like here Oracle is
> treating the stored procedure as a table, and is trying to return all the
> data as opposed to only the columns (where 1=1).
> Oracle publications do add the following objects on the Oracle server:
> http://msdn2.microsoft.com/en-us/library/ms152557.aspx
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
> news:uoQ%232bjwHHA.4800@.TK2MSFTNGP05.phx.gbl...
>
|||Configure the publication to use SQL Statements instead of the stored
procedures. In the Publication Properties, Articles tab, Select the
properties of the article and Insert, Update and delete deliver formats
select insert statements.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
news:OSxakFlwHHA.484@.TK2MSFTNGP06.phx.gbl...
> The Oracle side is actually the subscriber. It does appear that it is
> treating this as a table instead of a stored procedure. I can't see
> anything wrong with the article. The article has the insert and update
> commands defined as CALL stored procedures and the actual command names
> are the correct stored procedure names.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIyD82kwHHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||The reason we were drawn to the custom procedure is that we have long column
names > 30 characters in SQL Server and Oracle can only handle up to 30.
We are trying to handle the mapping in the custom stored procedure in Oracle
since there is no way to do this in SQL Server that we could come up with.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O$MTbRmwHHA.4076@.TK2MSFTNGP06.phx.gbl...
> Configure the publication to use SQL Statements instead of the stored
> procedures. In the Publication Properties, Articles tab, Select the
> properties of the article and Insert, Update and delete deliver formats
> select insert statements.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
> news:OSxakFlwHHA.484@.TK2MSFTNGP06.phx.gbl...
>

Custom conflict resolution problem

Hi all,

I

am doing a work were i have to syncronize data between a Publiser

(PC, SQL SERVER 2005), and a subscriber (Pocket PC, SQL CE MOBILE). I

studied lots of articles and i managed to put the syncronization to

work, the problem is in conflict resolution!

The subscriber can′t insert or delet, only update!!

For exmple: I have a table that contais the quantity of a product X,

for example 100 units, this goes to Pocket, but later arrive move 100

units that are increased in the PC, so we get 200 in the PC, (but 100

in Pocket), no problem if i syncronize now (Pocket will have 200 too),

but if remove in Pocket for example 50 units, i've changed the same

column in both Publisher and Subscriber database, if i syncronize now,

i'll have a conflict, the final result should be 150 of product X in

both databases (100 + 100 - 50), but the Publisher wins the conflict

and the final result is 200!

I never worked with stored procedures and in microsoft theres and article called How to: Implement a Stored Procedure-Based Custom Conflict Resolver for a Merge Article (Replication Transact-SQL Programming), but they dont explain it very well and i dont know how to do my own stored procedure and implement it to the merge article.

in my own stored procedure i'll have to do some calculations to get the

result i want, maybe using some table for the additions and

subtractions. (Does the com based Addition resolver do this for me?)

Maybe you have experienced some problem like this, and could help me!

How should i do the stored procedure?

Oh, i tried to use de addiction resolver and the average resolver

of com based but nothing happed, dont know why, i read some stuff and

i think the addition could solve my problem, but it doesn′t work, i read above what you said and installed service

pack 1 for sql 2005, but i instaled and nothing

happened!
When i change to the resolver that the subscriver wins, it works, why addition dont work?

Thanx

JohnCP, forget about the conflict resolvers for now, the question I have is regarding how you're creating your conflict. Please explain to me again the series of steps you took, is it:

1. delete 50 rows from pocket pc

2. update column at publisher

3. update column at subscriber

Is this correct? I cannot understand why you still have 200 rows existing, can you further explain?

|||

I split your post, please refrain from posting your same question to multiple threads.

Regarding custom resolver, you most likely will have to create your own custom COM resolver to resolve this type of an issue.

|||Hi Greg, thx for the answers
I solve the problem with business logic handlers.

Here is the code, maybe it could help more ppl

public class Class1 : Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule

{

// Variables to hold server names.

private string publisherName;

private string subscriberName;

// Implement the Initialize method to get publication

// and subscription information.

public override void Initialize(string publisher, string subscriber, string distributor,

string publisherDB, string subscriberDB, string articleName)

{

// Set the Publisher and Subscriber names.

publisherName = publisher;

subscriberName = subscriber;

}

// Declare what types of row changes, conflicts, or errors to handle.

override public ChangeStates HandledChangeStates

{

get

{

// Handle Subscriber inserts, updates and deletes.

return ChangeStates.UpdateConflicts |

ChangeStates.SubscriberUpdates | ChangeStates.PublisherUpdates;

}

}

//Treats update conflict
public override ActionOnUpdateConflict UpdateConflictsHandler(
DataSet publisherDataSet,
DataSet subscriberDataSet,
ref DataSet customDataSet,
ref ConflictLogType conflictLogType,
ref string customConflictMessage,
ref int historyLogLevel,
ref string historyLogMessage
)
{
//copies publisher dataset to customdataset
customDataSet = publisherDataSet.Copy();

//Quantity of the Publisher
int qPub = Int32.Parse(publisherDataSet.Tables[0].Rows[0]["Quantity "].ToString());

//Quantity of the Subscriber to Add to Publisher
int qSubA = Int32.Parse(subscriberDataSet.Tables[0].Rows[0]["AddToPub"].ToString());
//Quantity of the Subscriber to subtract to Publisher
int qSubS = Int32.Parse(subscriberDataSet.Tables[0].Rows[0]["SubToPub"].ToString());

int qFinal = qPub + (qSubA - qSubS);
//Insert final values in customDataSet that will be the data in both Pub and Subscriber
customDataSet.Tables[0].Rows[0]["Quantity "] = qFinal;
customDataSet.Tables[0].Rows[0]["AddToPub"] = 0;
customDataSet.Tables[0].Rows[0]["SubToPub"] = 0;

return ActionOnUpdateConflict.AcceptCustomConflictData;
}

//Treats normal update without conflict
public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
ref string historyLogMessage)
{
//if it's subscriber doing update -clean columns AddToPub, SubToPub
//to avoid errors
if (updateSource == SourceIdentifier.SourceIsSubscriber)
{
//copies dataset thats being updated to customdataset, where i'll make changes and return it
customDataSet = updatedDataSet.Copy();
//Insert final values in customDataSet that will be the data in both Pub and Subscriber
customDataSet.Tables[0].Rows[0]["AadicionarAPub"] = 0;
customDataSet.Tables[0].Rows[0]["AsubtrairAPub"] = 0;
// Accept the updated data in the Subscriber's data set and apply it to the Publisher.
return ActionOnDataChange.AcceptCustomData;
}
else
{
return base.UpdateHandler(updateSource, updatedDataSet,
ref customDataSet, ref historyLogLevel, ref historyLogMessage);
}
}

Custom business logic handler question

I am currenly have a simple merge replication topology. The publisher-distibutor is SQL Server 2005 and the subscriber is a SQL Server Express. The type of subscription is non anonymous pull.

I made a custom business logic handler class that is trying the following:

When a new record is inserted in a published table on the pubsliher, some additional records are added in a differenct table in subscriber. Then the "InsertHandler" method returns "AcceptData" in order to allow the agent to add the new record in current table also.

So I am establishing a new connection to the subscriber and I am trying to add the additional recs in the different table. The problem is that this different table is also published as read-only on the subscriber. So my insert fails saying that table is not updatable.

Is there any way to bypass this problem?

In fact I realised in general that when my custom logic handler performs some DML operations on the subscriber, these are NOT considered as part of the replication e.g. the NOT FOR REPLICATION constraints and triggers are active for these operations.

Is this normal?

Do you have these other tables set as download_only articles?

And more importantly why are you trying to insert at the subscriber when you are doing DML at the publisher? Couldn't you do these other DMLs at the publisher and let merge agent insert these new rows at the subscriber?

Custom business logic handler question

I am currenly have a simple merge replication topology. The publisher-distibutor is SQL Server 2005 and the subscriber is a SQL Server Express. The type of subscription is non anonymous pull.

I made a custom business logic handler class that is trying the following:

When a new record is inserted in a published table on the pubsliher, some additional records are added in a differenct table in subscriber. Then the "InsertHandler" method returns "AcceptData" in order to allow the agent to add the new record in current table also.

So I am establishing a new connection to the subscriber and I am trying to add the additional recs in the different table. The problem is that this different table is also published as read-only on the subscriber. So my insert fails saying that table is not updatable.

Is there any way to bypass this problem?

In fact I realised in general that when my custom logic handler performs some DML operations on the subscriber, these are NOT considered as part of the replication e.g. the NOT FOR REPLICATION constraints and triggers are active for these operations.

Is this normal?

Do you have these other tables set as download_only articles?

And more importantly why are you trying to insert at the subscriber when you are doing DML at the publisher? Couldn't you do these other DMLs at the publisher and let merge agent insert these new rows at the subscriber?