Pass dynamic arguement to sp_start_job

Question

Post reply

Pass dynamic arguement to sp_start_job

ImYourDBA

SSCertifiable

Points: 5654
More actions
June 8, 2010 at 12:54 pm

#140772

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/

Viewing 7 posts - 1 through 6 (of 6 total)

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

jjtetzlo SSCrazy Points: 2511 More actions · Answer 1

If the @JOBID parameter has the correct job_id, this should work:

exec msdb.dbo.sp_start_job @job_id = @JOBID

- Jeff

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 2

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/

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 3

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/

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

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

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 5

Awesome, that works like a charm. Thanks!

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 6

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/