Is complete control over subscriber-to-publisher inserts/updates possible?

Hi, we are using merge replication from our server (SQL Server 2005) to what
will soon be hundreds of subscribers. The subscribers are actually from a
Windows app that uses a SQLCE database. The client app is able to insert and
update records that are then merged up to the server. Everything I have
mentioned up to this point is already functional, but we recently realized
that more "control" over the replication process is needed. Here's what we
want to do: When a record that was inserted at the subscriber is replicated
up to the server, it must be caught and redirected to a stored procedure
which will perform the insert instead. This should be completely transparent
to the subscriber (meaning we can't return them an error code).
Unless I am mistaken the only way to get this kind of functionality is by
using a business logic handler. I have a sample which demonstrates some very
basic uses for the business logic handler but unfortunately it barely
scratches the surface of what is possible. This doc page from the BO explains
a scenario very similar to what we need (Custom Change Handling -> Apply
custom data), but I do not know how I would go about implementing it.
Can anyone who has done something like this before point me in the right
direction?
Using the BusinessLogic resolver is the correct way of doing it.
If the logic is such that it is only in one direction or only one table is
affected you can either use transactional replication from the subscribers
to the publisher (you will need to upgrade your subscribers to SQL Server
2005 standard for this), or have a trigger hanging of the publisher table so
when the insert arrives this trigger will process it and fire the stored
procedure.
You can use the session_property function to detect if the process doing the
dml is a replication process.
if convert(bit, sessionproperty('replication_agent'))=1
do work.
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
"Chris" <no@.spam.com> wrote in message
news:%2367uVkx0HHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hi, we are using merge replication from our server (SQL Server 2005) to
> what
> will soon be hundreds of subscribers. The subscribers are actually from a
> Windows app that uses a SQLCE database. The client app is able to insert
> and
> update records that are then merged up to the server. Everything I have
> mentioned up to this point is already functional, but we recently realized
> that more "control" over the replication process is needed. Here's what we
> want to do: When a record that was inserted at the subscriber is
> replicated
> up to the server, it must be caught and redirected to a stored procedure
> which will perform the insert instead. This should be completely
> transparent
> to the subscriber (meaning we can't return them an error code).
> Unless I am mistaken the only way to get this kind of functionality is by
> using a business logic handler. I have a sample which demonstrates some
> very
> basic uses for the business logic handler but unfortunately it barely
> scratches the surface of what is possible. This doc page from the BO
> explains
> a scenario very similar to what we need (Custom Change Handling -> Apply
> custom data), but I do not know how I would go about implementing it.
> Can anyone who has done something like this before point me in the right
> direction?
|||Thank you Hilary. I would prefer to use the business logic resolver though if
possible, but the more I dig into this it seems like it won't work the way I
need it to. I believe all I can do with it is modify the record sent from the
subscriber. Please correct me if I'm wrong, but it will not allow me to
*stop* the insert unless I outright reject it. Meaning I can't "redirect" the
insert to a SP and still tell the subscriber that everything went fine.
Using a trigger on the publisher table seems interresting. I will have to
look into that further. Thanks for your insight.
Chris
On 7/31/2007 6:39:27 AM, "Hilary Cotter" wrote:
> Using the BusinessLogic resolver is the correct way of doing it.
> If the logic is such that it is only in one direction or only one table is
> affected you can either use transactional replication from the subscribers
> to the publisher (you will need to upgrade your subscribers to SQL Server
> 2005 standard for this), or have a trigger hanging of the publisher table so
> when the insert arrives this trigger will process it and fire the stored
> procedure.
> You can use the session_property function to detect if the process doing the
> dml is a replication process.
> if convert(bit, sessionproperty('replication_agent'))=1
> do work.
>