Start SnapShot agent in T-SQL

  • 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]

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

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

  • 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]

  • 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.

  • [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]

  • 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 5 (of 5 total)

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