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
>

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

Execute phase Memory Error => dtsx finished: Canceled

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?

Execute phase Memory Error

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?