• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Starting a merge agent

I have managed to set up SQL merge replication between two servers in different locations. The merging seems to be going perfectly, with one minor hitch.  Just about every evening, the merge agent on my publisher doesn't seem to be able to find my subscriber. While this, in itself, is not too much of a problem, as there are tends to be no data to merge at these times, but it means that the identity ranges fill themselves up, and it becomes impossible to post records in one of the articles.

As far as I can tell, there are two potential work-arounds to this - either stop the servers from timing out, or set up a program or stored procedure to check if the agent is running, and if it isn't, start it again. In order to do either of these,  need three pieces of information:

1)   Is there a stored procedure to find the status of a merge agent?
2)   Is there a stored procedure to start a merge agent?
3)   Is there a setting somewhere, presumably in the merge agent profile, that detemrines how long it will attempt to conect to a subscriber before timing out?

Thanks in advance

  • 3
  • 2
1 Solution
alanjbrownAuthor Commented:
Quick clarification - when the publisher cannot find the subscriber, it stops attempting to connect, so merges cannot take place.
create procedure p_start_merge_job
    @publication_name sysname,
    @debug bit = 0
-- Start the merge replication job associated with the parameter name
-- 10SEP00 Bodhi
declare @jobname sysname
select @jobname = name from msdb..sysjobs
where name like '%'+@publication_name+'%'
if @debug <> 0 begin
    print @jobname
if @jobname is null begin
    RAISERROR ('No job found for publication name, "%s"',16,1, @publication_name)
    return 16
exec msdb..sp_start_job  @jobname
alanjbrownAuthor Commented:
Thank you, Bodhi. Taht deals with question 2) perfectly. Any chance you can answer parts 1) and 3) as well?

In addition, is there a similar stored procedure for re-starting a connection between two servers? I am prepared to award more points for this.
Part 1) use sp_help_job.
I'm leaving part 3 unanswered.
alanjbrownAuthor Commented:
Okay. Bodhi, you will definately get the answer here. Out of interest, can you do the extra one as well?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now