Start SnapShot agent in T-SQL

  • Jules Bonnot

    SSCertifiable

    Points: 5488

    SQL2005

     

    I need to automate the starting of the snapshot agent.  At the moment i have to go into replication monitor right click on snapshot agent and click start.

    Is this possible in T-SQL or do you have to use the DOS cmd. Any sample code would be nice,

    Thanks,

    Jules

    www.sql-library.com[/url]

  • Dinakar Nethi-176633

    SSCarpal Tunnel

    Points: 4689

    I am not sure..but you can always run profiler and see whats being called..

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • Pedro R. Lopez

    SSC Eights!

    Points: 902

    Every time the you create a publication a jobs is created in sql server with this name

    [SERVER_NAME]-[DATABASE_NAME]-publ_[PUBL_NAME]

    don't you have it on you job list ?

    there is an step inside the job that look like this

     

    -Publisher [SQL05] -PublisherDB [db_1] -Distributor [SQL05] -Publication [publ_db_1] -ReplicationType 2 -DistributorSecurityMode 1

    Pedro R. Lopez
    http://madurosfritos.blogspot.com/[/url]

  • CJ007

    SSC Enthusiast

    Points: 105

    In Sql Server 2005, you can use the sp_startpublication_snapshot stored procedure to achieve this, using the following format:

    sp_startpublication_snapshot [ @publication = ] 'publication'

    [ , [ @publisher = ] 'publisher' ]

    (omit the publisher out if you are using a SQL Server publisher).

    I do not know if this is appropriate to Sql Server 7 or 2000 though.

  • Abrar Ahmad_

    SSCarpal Tunnel

    Points: 4208

    [font="Verdana"]

    What about to call for distribution agent in Push Down subscription (snapshot replication) - which job to execute?

    and the syntax listed in Job is not of Tsql SP? So how to manage both .. if we can call with the help of SP and with the help of Job syntax?

    Thank you!

    [/font]

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Internally, SQL Server matches the snapshot agents to SQL Jobs. You can access this information from the distribution database:

    select *

    from dbo.MSsnapshot_agents as mssa

    inner join msdb.dbo.sysjobs_view as sjv

    on mssa.job_id = sjv.job_id

    inner join msdb.dbo.sysjobsteps as sjs

    on sjv.job_id = sjs.job_id

    where sjv.category_id = 15

    and sjs.subsystem = N'Snapshot'

    and mssa.dynamic_filter_login is NULL

    and mssa.dynamic_filter_hostname is NULL

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply