Executing code on publisher after replication completes

I am using anonymous merge "pull" subscriptions to synchronize a
SqlServerCE database and a SQL 2000 database. The handheld (PocketPC)
users are entering data, which is replicated to the SQL 2000 database.
This is all working as expected.
The only thing I am missing is the ability in SQL 2000 to detect when
one of the handheld/subscribers has replicated. In particular, the SQL
2000 database needs to run code (preferably a stored procedure) when a
subscriber has finished replicating. I do not know how to detect when
replication is complete -- how do I do that?
Thanks,
Rod Early
Why can't you have a statement after the sync?
Hilary Cotter
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
"Rod" <REarly@.jswcoinc.com> wrote in message
news:1132605090.860477.266420@.o13g2000cwo.googlegr oups.com...
>I am using anonymous merge "pull" subscriptions to synchronize a
> SqlServerCE database and a SQL 2000 database. The handheld (PocketPC)
> users are entering data, which is replicated to the SQL 2000 database.
> This is all working as expected.
> The only thing I am missing is the ability in SQL 2000 to detect when
> one of the handheld/subscribers has replicated. In particular, the SQL
> 2000 database needs to run code (preferably a stored procedure) when a
> subscriber has finished replicating. I do not know how to detect when
> replication is complete -- how do I do that?
> Thanks,
> Rod Early
>
|||Thanks Hilary. For clarification, the code needs to run on the
publisher, the SQL 2000 database. I already have some code that runs
after sync on the handheld.
The main reason for needing to run code after sync on the publisher is
to update the a few "date received" columns so there is a record of
when the data was actually received at the server, as opposed to when
it was entered on the handheld.
Certainly on the handheld I could run code to update the "date
received" columns prior to sync. But two issues come to mind -- if the
sync fails for any reason, "date received" won't be correct because the
server wouldn't have actually received the rows. Secondly, the dates
in those fields would be the handheld's date/time (which would vary per
device because of time zone and how the device clock is set), rather
than when it was received at the server -- regardless of the handheld's
clock.
Thanks,
Rod
|||You may be able to use triggers as an option. These will only run at the
server of course.
However, you may want to consider whether you like the idea of updating a
table that has just been synchronised. Any update will cause it to be
transfered back to the device in it's newly updated state. Unless of source
you are using a reference table to point back at the changed data.
"Rod" wrote:

> Thanks Hilary. For clarification, the code needs to run on the
> publisher, the SQL 2000 database. I already have some code that runs
> after sync on the handheld.
> The main reason for needing to run code after sync on the publisher is
> to update the a few "date received" columns so there is a record of
> when the data was actually received at the server, as opposed to when
> it was entered on the handheld.
> Certainly on the handheld I could run code to update the "date
> received" columns prior to sync. But two issues come to mind -- if the
> sync fails for any reason, "date received" won't be correct because the
> server wouldn't have actually received the rows. Secondly, the dates
> in those fields would be the handheld's date/time (which would vary per
> device because of time zone and how the device clock is set), rather
> than when it was received at the server -- regardless of the handheld's
> clock.
> Thanks,
> Rod
>

Executing a unit of work without replication

We are using SQL Server 2000 on Win2003.
I would like to do something like the following on a replicated system.
BEGIN WORK WITHOUT REPLICATION;
...
<Some insert, update or delete SQL>
...
COMMIT;
Is there any way to achieve this non-replicated transaction with SQL
Server. This needs to be an on-line activity in parallel with other
replicated transactions.
Thanks,
Redden96
if there is something about the nature of the update you can
encapsulate logic into your replication stored procedures to skip these
transactions. For instance if you have a column which has a unique
value for this type of work you do not want replicated you could trap
for this value in the replication stored procedures.
Another option is to put filters on your articles which look like this
select * from publishedtables where 1=1
Then in your filter procs change them to always return 0 when you want
these transactions skipped. When you want replication to pick up
transactions again, change it to 1.
|||Thanks Hilary. I was hoping for something a little less invasive, but I
can make a solution similar to the one you describe work for me.

execute stored procedure durning merge replication

Hello
I have merge replication with dynamic filters and I have to create
dynamically table just before merge replication starts. Is there any
possibility of exec stored procedure durnig merge replication or just
before it?
Thanks in advance,
marcin
Hi
My problem is more complicated.
I create dynamicly subscription from Pocket PC where I set Host_Name for
dynamic filters.
How can I add merge agent job from source code ?
Any ideas ?
Thanks,
Marcin
Paul Ibison wrote:

> Sure - just add another step to the merge agent's job and
> change the workflow accordingly. I have had issues with
> using sp_start_job in job steps - there is no guarantee
> that a step will complete fully before the next one
> starts, but in the case of a script call this should be
> ok.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>