Start SnapShot agent in T-SQL

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  July 11, 2006 at 7:23 am

  #94480

  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

  More actions

  July 12, 2006 at 11:59 am

  #648774

  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

  More actions

  July 12, 2006 at 1:45 pm

  #648809

  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

  More actions

  December 10, 2011 at 11:18 am

  #1419739

  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: 4244

  More actions

  April 2, 2014 at 10:33 am

  #1702549

  [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

  More actions

  July 14, 2015 at 12:46 pm

  #1812436

  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