June 8, 2010 at 12:54 pm
I am writing some custom scripts that create our replication topology for a disaster recovery plan and the last step is to run the section where it will start the snapshot agent. So, I'm trying to figure out how to use T-SQL to start the snapshot agent.
I know this much, I would use the sp_start_job SP in MSDB and pass it the @job_id argument (or @job_name). However, since this is for a DR plan and we have several servers and topologies for replication I would like to have it use another SP that I wrote that gets the job_id of the REPL-Snapshot job from the sp_help_jobs SP to get the value for @job_id.
My question is, how do I pass a variable into the statement? And how do I combine all of it? Below is what I have so far and I know that it's not complete or right, but wanted to give you an idea of the direction I was moving.
DECLARE @JOB TABLE(JOB_ID nvarchar(50)),
@JOBID nvarchar(50)
INSERT @JOB (JOB_ID)
exec dbo.GetJobIDForReplicationSnapshot
set @JOBID = select * from @job
USE [msdb]
exec sp_start_job @job_id = N''+@JOB+'';
GO
So the dbo.GetJobIDForReplicationSnapshot SP that I wrote comes back with the appropriate value for the job_id. i just need to figure out how to use that value in the "exec sp_start_job @job_id = " argument.
Any help is appreciated.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 8, 2010 at 1:19 pm
If the @JOBID parameter has the correct job_id, this should work:
exec msdb.dbo.sp_start_job @job_id = @JOBID
- Jeff
June 8, 2010 at 1:26 pm
I think I'm missing something else here as well. I get a couple errors on the declare statement syntax (on the , separating the two variables and it says that I must declare the scalar variable @JOBID). Is it not possible to declare other variables when you use the TABLE type?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 8, 2010 at 1:40 pm
Should it be something closer to this? Still not right as it is asking me to declare scalar value even though I'm only returning a single value. How should I rewrite this to use the results from GetJobIDForReplicationSnapshot as a varchar variable instead of a table? I assume that is why I can't use it for the argument for sp_start_job.
DECLARE @JOB TABLE(JOB_ID nvarchar(50))
INSERT @JOB (JOB_ID)
exec dbo.GetJobIDForReplicationSnapshot
--select * from @JOB
USE [msdb]
exec sp_start_job @job_id = @JOB
GO
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 8, 2010 at 2:00 pm
More like this:
DECLARE @JOB TABLE(JOB_ID nvarchar(50))
DECLARE @JOBID nvarchar(50)
INSERT into @JOB (JOB_ID)
exec dbo.GetJobIDForReplicationSnapshot
set @JOBID = (select * from @job)
USE [msdb]
exec sp_start_job @job_id = @jobid
GO
June 8, 2010 at 2:06 pm
Awesome, that works like a charm. Thanks!
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 9, 2010 at 12:31 pm
By the way, I'm feeling a little foolish for not seeing this sooner but for anyone else looking for it, there is a special system stored procedure that is already used to start the snapshot agent.
It would be implemented as such:
sp_startpublication_snapshot @publication = N'[pubname]'
:laugh:
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply