"Hilary Cotter" <hilaryK@att.net> wrote in message
news:03c801c39339$bf715380$a101280a@phx.gbl...
> 1) I would investigate creating a fourth step in you merge
> agent properties.
>
> For instance expand replication monitor, then expand the
> replication agents folder, expand merge agents, right
> click on your agent, select properties, then steps.
>
> Add a step which after the success of the Detect nonlogged
> agent shutdown, will be run, but not on job failure.
>
> This might do what you are looking for.
>
> 2) With merge/transactional/snapshot the hostname
> paremeter will evaluate correctly if and only if you use a
> pull. Using a push, the hostname will evaluate to be the
> hostname of the publisher - which probably is not what you
> are looking for.
>
> In your snapshot you probably want to do a dynamic
> snapshot to get your filtered data over there.
> >-----Original Message-----
> >Hello,
> >
> >I've encapsulated some logic into a stored procedure that
> I need to have my
> >publisher (which is also my distributor) run each time
> replication is done.
> >We are using merge replication with dynamic snapshots. I
> have two problems
> >that I have encountered:
> >
> >1) Where do I place the code to execute this stored
> procedure? I want it
> >ran each time a subscriber replicates with the
> publisher. If I could store
> >it on the publisher somewhere in the replication 'job',
> and have that logic
> >replicated automatically to each subscriber, that would
> be great.
> >
> >2) I use HOST_NAME() in this stored procedure to ensure
> that my query only
> >returns one row. If my subscriber is connected to the
> publisher, and
> >HOST_NAME() is used in the proc which is actually ran on
> the publisher, will
> >it use the value of the subscriber or the publisher? In
> order to work
> >correctly, it must use the subscriber's name.
> >
> >3) Am I approaching this incorrectly, or should I just
> determine where the
> >proc needs to run, include it in replication, and modify
> it manually on each
> >machine, substituting the actual value of the workstation
> name for
> >HOST_NAME()?
> >
> >Any help would be greatly appreciated!
> >
> >Best Regards,
> >Brad
> >
> >
> >.
> >

"Hilary Cotter" <hilaryk@att.net> wrote in message
news:OnMIvZElDHA.3504@TK2MSFTNGP11.phx.gbl...
> yes subsequent agents with the 4th step on restart will run the proc.
>
> You can't do the fully qualified object naming scheme as replication is
> carried out via a remote server as opposed to a linked server process -
> essential ODBC.
>
> I think you should have a look at running the proc on the publisher but
then
> using sp_addscriptexec (I think that is the name of the proc) and this
will
> execute something on all of your subscribers. I believe the @@servername
> parameter will resolve correctly when using sp_addscriptexec.
>
>
> "Brad M." <officespaceatshawdotca.REMOVEMEANDDOT> wrote in message
> news:um0SFy0kDHA.2528@TK2MSFTNGP10.phx.gbl...
> > Hi Hilary,
> >
> > Thanks for the response. Our subscriptions are indeed pull
subscriptions,
> > not push. If I place this proc on our publisher, and modify the merge
> agent
> > properties, does that mean that subsequent synchronizations will
> > automatically complete this new step in their synchronization?
> >
> > Also, one more question in relation to this...
> >
> > When this proc is ran, if the rowcount is not zero, an update statement
is
> > supposed to be executed on the subscriber to the effect of UPDATE
> > Synchronization SET SyncDate = GETDATE(). Because this proc is being
ran
> on
> > the publisher, I assume that I'll have to use the four-part naming
> > convention to accomplish what I want. But can I do that? The only way
I
> > know what the subscriber's name is is by using HOST_NAME(). Can I use
> > HOST_NAME().dbname.dbo.Synchronization or could I accomplish what I want
> > using OPENROWSET?
> >
> > Thank you very much!
> >
> > Best Regards,
> > Brad
> >
> > "Hilary Cotter" <hilaryK@att.net> wrote in message
> > news:03c801c39339$bf715380$a101280a@phx.gbl...
> > > 1) I would investigate creating a fourth step in you merge
> > > agent properties.
> > >
> > > For instance expand replication monitor, then expand the
> > > replication agents folder, expand merge agents, right
> > > click on your agent, select properties, then steps.
> > >
> > > Add a step which after the success of the Detect nonlogged
> > > agent shutdown, will be run, but not on job failure.
> > >
> > > This might do what you are looking for.
> > >
> > > 2) With merge/transactional/snapshot the hostname
> > > paremeter will evaluate correctly if and only if you use a
> > > pull. Using a push, the hostname will evaluate to be the
> > > hostname of the publisher - which probably is not what you
> > > are looking for.
> > >
> > > In your snapshot you probably want to do a dynamic
> > > snapshot to get your filtered data over there.
> > > >-----Original Message-----
> > > >Hello,
> > > >
> > > >I've encapsulated some logic into a stored procedure that
> > > I need to have my
> > > >publisher (which is also my distributor) run each time
> > > replication is done.
> > > >We are using merge replication with dynamic snapshots. I
> > > have two problems
> > > >that I have encountered:
> > > >
> > > >1) Where do I place the code to execute this stored
> > > procedure? I want it
> > > >ran each time a subscriber replicates with the
> > > publisher. If I could store
> > > >it on the publisher somewhere in the replication 'job',
> > > and have that logic
> > > >replicated automatically to each subscriber, that would
> > > be great.
> > > >
> > > >2) I use HOST_NAME() in this stored procedure to ensure
> > > that my query only
> > > >returns one row. If my subscriber is connected to the
> > > publisher, and
> > > >HOST_NAME() is used in the proc which is actually ran on
> > > the publisher, will
> > > >it use the value of the subscriber or the publisher? In
> > > order to work
> > > >correctly, it must use the subscriber's name.
> > > >
> > > >3) Am I approaching this incorrectly, or should I just
> > > determine where the
> > > >proc needs to run, include it in replication, and modify
> > > it manually on each
> > > >machine, substituting the actual value of the workstation
> > > name for
> > > >HOST_NAME()?
> > > >
> > > >Any help would be greatly appreciated!
> > > >
> > > >Best Regards,
> > > >Brad
> > > >
> > > >
> > > >.
> > > >
> >
> >
>
>

"Brad M." <officespaceatshawdotca.REMOVEMEANDDOT> wrote in message
news:eg%23kg8ElDHA.708@TK2MSFTNGP10.phx.gbl...
> Hi Hilary,
>
> I'm not quite sure what you mean. If i'm understanding you correctly, I
> need to modify my stored procedure? Code is below...
>
> SELECT DISTINCT MH.agent_id, MA.subscriber_name, MA.publisher_db,
> MH.runstatus, MAX(MH.start_time) AS synctime
> FROM Distribution.dbo.MSMerge_History MH
> INNER JOIN Distribution.dbo.MSMerge_Agents MA
> ON MA.id = MH.agent_id
> WHERE runstatus = 2
> AND publisher_db = 'LWTData'
> AND subscriber_name = HOST_NAME()
> GROUP BY agent_id, subscriber_name, runstatus, publisher_db
>
> IF @@ROWCOUNT <> 0
> BEGIN
> /* Update Statement Here */
> END
>
> As you can see, it requires information from the distribution database,
> meaning that I need to be able to do (something, anything) to be able to
get
> that four-part name to work so the update statement applies to the
> subscriber. Could you give me an example of how I could incorporate this
> sp_addscriptexec to have this work? I apologize in advance, because this
is
> my first time working with replication.
>
> Thank you very much.
>
> Regards,
> Brad
>
> "Hilary Cotter" <hilaryk@att.net> wrote in message
> news:OnMIvZElDHA.3504@TK2MSFTNGP11.phx.gbl...
> > yes subsequent agents with the 4th step on restart will run the proc.
> >
> > You can't do the fully qualified object naming scheme as replication is
> > carried out via a remote server as opposed to a linked server process -
> > essential ODBC.
> >
> > I think you should have a look at running the proc on the publisher but
> then
> > using sp_addscriptexec (I think that is the name of the proc) and this
> will
> > execute something on all of your subscribers. I believe the
@@servername
> > parameter will resolve correctly when using sp_addscriptexec.
> >
> >
> > "Brad M." <officespaceatshawdotca.REMOVEMEANDDOT> wrote in message
> > news:um0SFy0kDHA.2528@TK2MSFTNGP10.phx.gbl...
> > > Hi Hilary,
> > >
> > > Thanks for the response. Our subscriptions are indeed pull
> subscriptions,
> > > not push. If I place this proc on our publisher, and modify the merge
> > agent
> > > properties, does that mean that subsequent synchronizations will
> > > automatically complete this new step in their synchronization?
> > >
> > > Also, one more question in relation to this...
> > >
> > > When this proc is ran, if the rowcount is not zero, an update
statement
> is
> > > supposed to be executed on the subscriber to the effect of UPDATE
> > > Synchronization SET SyncDate = GETDATE(). Because this proc is being
> ran
> > on
> > > the publisher, I assume that I'll have to use the four-part naming
> > > convention to accomplish what I want. But can I do that? The only
way
> I
> > > know what the subscriber's name is is by using HOST_NAME(). Can I use
> > > HOST_NAME().dbname.dbo.Synchronization or could I accomplish what I
want
> > > using OPENROWSET?
> > >
> > > Thank you very much!
> > >
> > > Best Regards,
> > > Brad
> > >
> > > "Hilary Cotter" <hilaryK@att.net> wrote in message
> > > news:03c801c39339$bf715380$a101280a@phx.gbl...
> > > > 1) I would investigate creating a fourth step in you merge
> > > > agent properties.
> > > >
> > > > For instance expand replication monitor, then expand the
> > > > replication agents folder, expand merge agents, right
> > > > click on your agent, select properties, then steps.
> > > >
> > > > Add a step which after the success of the Detect nonlogged
> > > > agent shutdown, will be run, but not on job failure.
> > > >
> > > > This might do what you are looking for.
> > > >
> > > > 2) With merge/transactional/snapshot the hostname
> > > > paremeter will evaluate correctly if and only if you use a
> > > > pull. Using a push, the hostname will evaluate to be the
> > > > hostname of the publisher - which probably is not what you
> > > > are looking for.
> > > >
> > > > In your snapshot you probably want to do a dynamic
> > > > snapshot to get your filtered data over there.
> > > > >-----Original Message-----
> > > > >Hello,
> > > > >
> > > > >I've encapsulated some logic into a stored procedure that
> > > > I need to have my
> > > > >publisher (which is also my distributor) run each time
> > > > replication is done.
> > > > >We are using merge replication with dynamic snapshots. I
> > > > have two problems
> > > > >that I have encountered:
> > > > >
> > > > >1) Where do I place the code to execute this stored
> > > > procedure? I want it
> > > > >ran each time a subscriber replicates with the
> > > > publisher. If I could store
> > > > >it on the publisher somewhere in the replication 'job',
> > > > and have that logic
> > > > >replicated automatically to each subscriber, that would
> > > > be great.
> > > > >
> > > > >2) I use HOST_NAME() in this stored procedure to ensure
> > > > that my query only
> > > > >returns one row. If my subscriber is connected to the
> > > > publisher, and
> > > > >HOST_NAME() is used in the proc which is actually ran on
> > > > the publisher, will
> > > > >it use the value of the subscriber or the publisher? In
> > > > order to work
> > > > >correctly, it must use the subscriber's name.
> > > > >
> > > > >3) Am I approaching this incorrectly, or should I just
> > > > determine where the
> > > > >proc needs to run, include it in replication, and modify
> > > > it manually on each
> > > > >machine, substituting the actual value of the workstation
> > > > name for
> > > > >HOST_NAME()?
> > > > >
> > > > >Any help would be greatly appreciated!
> > > > >
> > > > >Best Regards,
> > > > >Brad
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>
>

"Brad M." <officespaceatshawdotca.REMOVEMEANDDOT> wrote in message
news:um0SFy0kDHA.2528@TK2MSFTNGP10.phx.gbl...
> Hi Hilary,
>
> Thanks for the response. Our subscriptions are indeed pull subscriptions,
> not push. If I place this proc on our publisher, and modify the merge
agent
> properties, does that mean that subsequent synchronizations will
> automatically complete this new step in their synchronization?
>
> Also, one more question in relation to this...
>
> When this proc is ran, if the rowcount is not zero, an update statement is
> supposed to be executed on the subscriber to the effect of UPDATE
> Synchronization SET SyncDate = GETDATE(). Because this proc is being ran
on
> the publisher, I assume that I'll have to use the four-part naming
> convention to accomplish what I want. But can I do that? The only way I
> know what the subscriber's name is is by using HOST_NAME(). Can I use
> HOST_NAME().dbname.dbo.Synchronization or could I accomplish what I want
> using OPENROWSET?
>
> Thank you very much!
>
> Best Regards,
> Brad
>
> "Hilary Cotter" <hilaryK@att.net> wrote in message
> news:03c801c39339$bf715380$a101280a@phx.gbl...
> > 1) I would investigate creating a fourth step in you merge
> > agent properties.
> >
> > For instance expand replication monitor, then expand the
> > replication agents folder, expand merge agents, right
> > click on your agent, select properties, then steps.
> >
> > Add a step which after the success of the Detect nonlogged
> > agent shutdown, will be run, but not on job failure.
> >
> > This might do what you are looking for.
> >
> > 2) With merge/transactional/snapshot the hostname
> > paremeter will evaluate correctly if and only if you use a
> > pull. Using a push, the hostname will evaluate to be the
> > hostname of the publisher - which probably is not what you
> > are looking for.
> >
> > In your snapshot you probably want to do a dynamic
> > snapshot to get your filtered data over there.
> > >-----Original Message-----
> > >Hello,
> > >
> > >I've encapsulated some logic into a stored procedure that
> > I need to have my
> > >publisher (which is also my distributor) run each time
> > replication is done.
> > >We are using merge replication with dynamic snapshots. I
> > have two problems
> > >that I have encountered:
> > >
> > >1) Where do I place the code to execute this stored
> > procedure? I want it
> > >ran each time a subscriber replicates with the
> > publisher. If I could store
> > >it on the publisher somewhere in the replication 'job',
> > and have that logic
> > >replicated automatically to each subscriber, that would
> > be great.
> > >
> > >2) I use HOST_NAME() in this stored procedure to ensure
> > that my query only
> > >returns one row. If my subscriber is connected to the
> > publisher, and
> > >HOST_NAME() is used in the proc which is actually ran on
> > the publisher, will
> > >it use the value of the subscriber or the publisher? In
> > order to work
> > >correctly, it must use the subscriber's name.
> > >
> > >3) Am I approaching this incorrectly, or should I just
> > determine where the
> > >proc needs to run, include it in replication, and modify
> > it manually on each
> > >machine, substituting the actual value of the workstation
> > name for
> > >HOST_NAME()?
> > >
> > >Any help would be greatly appreciated!
> > >
> > >Best Regards,
> > >Brad
> > >
> > >
> > >.
> > >
>
>