Script to find the snapshot job for my replication publisher

Question

Is there a way, via script, to see what the name of the job is that will fire the snapshot for my replication?

This is for my Development environment. I am rebuilding my databases automatically via a visual studio database project. I have my replication setup automated, but I still have to fire the snapshot job manually in the morning.

I would like to be able to call:

sp_start_job 'JobThatWillTakeTheSnapShotForMyPublisher'

The problem is that the name of the job changes after every time I run my auto deploy (it adds a number to the end of the job name.)

If there is no way to find this out, is there a way to deterministically set the name of the job myself?

Answer 1

You can find snapshot jobid in replication tables syspublications for transactional replication and sysmergepublications for merge replication, for example:

declare @jobId uniqueidentifier

use <MyPublicationDB>
set @jobId = (
    select snapshot_jobid from syspublications 
    where name = '<MyPublication>')

select @jobId

Answer 2

This is what I came up with:

declare @jobId uniqueidentifier
declare @jobName sysname

select  @jobId = jobs.job_id, @jobName = jobs.name
from    msdb.dbo.sysjobs jobs (nolock)
        join msdb.dbo.syscategories categories (nolock)
          on jobs.category_id = categories.category_id
where   categories.name = 'REPL-Snapshot'
        and jobs.name like '%MyPublisherNameHere%'

select @jobId, @jobName   

exec sp_start_job @job_id = @jobId