Pass dynamic arguement to sp_start_job

Question

Post reply

Pass dynamic arguement to sp_start_job

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

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

jjtetzlo SSCrazy Points: 2561 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/