Script to find the snapshot job for my replication publisher
Is there a way, via script, to see what the name of the job is that will fire the snapshot for my replication?
This i开发者_Python百科s 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?
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
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
精彩评论