Executing multiple jobs via TSQL

• NBBCReporting

  SSC Rookie

  Points: 31

  More actions

  August 12, 2009 at 11:56 am

  #137251

  Hi,

  We are looking to run all of our SSRS subscription jobs via TSQL and SQL doesnt seem to like to execute it via a subquery. Any Assistance that can help us?

  Here is a sample of what we are doing, obviously the subquery is declared and set, we're just leaving it out for the sake of saving the time to write it out 😀

  USE msdb

  EXEC sp_start_job @job_name in (@subquery)

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  August 12, 2009 at 2:21 pm

  #1038291

  try this...

  declare @jobs table(name varchar(50),id int identity)

  declare @JobName varchar(50)

  declare @max-2 int

  declare @count int

  declare @sql varchar(5000)

  insert into @jobs(name)

  --replace next line w/ subquery

  select jobname from blah blah blah

  select @count = 1,@max= max(id) from @jobs

  while @count <= @max-2

  BEGIN

  select @Jobname = name from @jobs where id = @count

  execute sp_start_job @jobName

  select @count = @count + 1

  END