Starting a merge agent

Posted on 2005-04-08
Last Modified: 2010-08-05
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

Question by:alanjbrown
    LVL 1

    Author Comment

    Quick clarification - when the publisher cannot find the subscriber, it stops attempting to connect, so merges cannot take place.
    LVL 3

    Accepted Solution

    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
    LVL 1

    Author Comment

    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.
    LVL 3

    Expert Comment

    Part 1) use sp_help_job.
    I'm leaving part 3 unanswered.
    LVL 1

    Author Comment

    Okay. Bodhi, you will definately get the answer here. Out of interest, can you do the extra one as well?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now