If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: TSQL to run all "SQL Jobs"....umm. Help.

I have the challenge to run TSQL to execute in sequence all SQL Jobs on a machine and then capture the start/end times. Ok, simple enough. So, I came up with this bit of code. All of the jobs run, but the very last step is where I'm trying to echo the results to the client. I always get the error, "Result Set move failed. Rowset position cannot be restarted"

I've tried a ton of things and am at a loss.

Help o-great-ones!

Code:

DECLARE @cnt int,
@totalJobs int,
@loopcount int,
@job_id varchar(128),
@max int,
@name nvarchar(128),
@session_id varchar(32),
@start_date varchar(32),
@stop_date varchar(32),
@jobIFound varchar(128),
@JobIDIRan varchar(128),
@myOutput varchar(1000),
@TheDetails varchar(1000),
@RunStatus int,
@Date_Executed varchar(255),
@secs_duration varchar(255),
@JobStatus Varchar(255),
@TheJobID varchar(64),
@entity varchar(255);
--
-- Going to create a temp table with 2 fields: Unique number & data
-- There are two log entries. First when the job starts and the second is a
-- summary of what happened. (start, end, ID etc...) The temp table is
-- automatically purged after the SQL session terminates but the output
-- is captured by Zena and saved in the task output so we can do something
-- with it later on.
-- **The data types don't matter as the output will be inside the task**
--
DECLARE @ResultsTable2 TABLE
(
LineNumber int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Node varchar(255),
ProductionDate varchar(255),
StartDateTime varchar(255),
EndDateTime varchar(255),
ReturnCode varchar(255),
Environment varchar(255),
ProcessType varchar(255),
BackupType varchar(255),
SQLJobName varchar(255),
BackupStatus varchar(255),
Complete varchar(16),
SessionID int
);
DECLARE @AllSQLJobs2 TABLE
(
LineNumber int IDENTITY(1,1) NOT NULL PRIMARY KEY,
alljobsjob_ID varchar(128),
alljobsName varchar(128)
);
Set @Entity = '[TASK.VARIABLE.Node]'
insert into @allsqljobs2 (alljobsjob_id, alljobsname)
select job_id, name
from msdb..sysjobs
where name like '%BackupLS%';
select @totalJobs = count(linenumber) from @allsqljobs2
set @loopcount = 1
WHILE @loopcount <= @totalJobs
BEGIN
SELECT @name = alljobsname
FROM @allSQLJobs2
WHERE lineNumber = convert(int, @loopcount)
-- Look at the temp table to figure out what the first job name is and assign it to
-- the variable.
SET @max = 7200 -- if we poll more than 10 hrs (10-second polls), something is wrong
-- 60 mins x 10hrs X 12 10 second polls/min = 7200
-- First, we need to figure out the unique number associated with the
-- SQL Job Name. Humans can read SQL Job names, not the Job ID.
use msdb
SELECT @job_id = job_id
FROM msdb..sysjobs
WHERE name = @name
and
enabled = 1
-- if enabled is 0, then it means the job is disabled
IF @job_id IS NOT NULL
BEGIN
--set @JobIFound = '@name = ' + @name
--set @JobIDIRan = '@job_id = ' + convert(varchar(255), @job_id)
--SP will execute the job by the ID
use msdb
EXEC sp_start_job
@job_id = @job_id
SET @cnt = 0
WHILE @cnt < @max
BEGIN
SET @cnt = @cnt + 1
-- The convert startements change the awful field types for
-- start date/time from INT into a datetime. Type 120 = yyyy-mm-dd hh:mi:ss (in 24hr clock)
SELECT @session_id = CONVERT(varchar(32), session_id),
@start_date = CONVERT(varchar(32), start_execution_date, 120),
@stop_date = CONVERT(varchar(32), stop_execution_date, 120)
FROM msdb..sysjobactivity
WHERE job_id = @job_id
-- We are going to look to see when the stop date appears
-- when it does, the job is finished and this loop will stop
IF @stop_date IS NOT NULL
BREAK
-- Take the data you just assembled above and capture it in a temp table
INSERT INTO @resultsTable2 (Node, ProductionDate, startDateTime, EndDateTime,
ReturnCode, Environment, ProcessType, BackupType, SQLJobName,
BackupStatus, SessionID)
VALUES (@entity, '[PROCESS.PRODUCTION_DATE]',CONVERT(varchar, GetDate(), 120),
ISNULL(@stop_date, ''),'','[TASK.VARIABLE.Environment]','SQL Instance',
'SQL Instance Backup', @name, 'Not Finished', ISNULL(@session_id, ''));
-- Hang out 10 secnds and then re-query the DB for status on the job
WAITFOR DELAY '00:00:10'
END
--
-- Take the data you just assembled above and capture it in a temp table
-- Possible enhancements would be to make a new query into the
-- sysjobhistory table and pull out the last runs run_status etc...
-- include that data here so it can be reported on.
--
-- Looks like the job ended. We now need to lookup in another table
-- it figure out if the job was successful or not.
select @TheJobID = job_id,
@RunStatus = Run_status
from msdb..sysjobhistory
where step_id=0 --Job Outcome
and job_ID = @Job_ID
and convert(datetime,convert(varchar(8),run_date))+' '
+stuff(stuff(right(1000000+run_time,6),3,0,':'),6,0,':')
>= dateadd(Hh,-1,getdate()) --Run_Date is the start date/time of this step. Since this is the last step, it is the end of the job.
if @RunStatus = 0 set @JobStatus = 'Failed'
if @RunStatus = 1 set @JobStatus = 'Succeeded'
if @RunStatus = 2 set @JobStatus = 'Retry'
if @RunStatus = 3 set @JobStatus = 'Canceled'
INSERT INTO @resultsTable2 (Node, ProductionDate, startDateTime, EndDateTime,
ReturnCode, Environment, ProcessType, BackupType, SQLJobName,
BackupStatus, SessionID, Complete)
VALUES (@entity, '[PROCESS.PRODUCTION_DATE]',ISNULL(@start_date, ''),
ISNULL(@stop_date, ''),@RunStatus,'[TASK.VARIABLE.Environment]','SQL Instance',
'SQL Instance Backup', @name, @Jobstatus, ISNULL(@session_id, ''),'Done');
WAITFOR DELAY '00:00:10' ;
END
set @loopcount = @loopcount + 1
END ;
-- After the job has finished or the timer has expired, we need to grab all
-- of the details we captured earlier and saved in the temp table and return the
-- results to this task so it can be moved somewhere else
--
-- Depending on how long the job takes, there maybe multiple lines of data
-- in the temp table. We only want the last entry
--
DECLARE ResultsTable2cursor CURSOR FOR
SELECT LineNumber, Node, ProductionDate, StartDateTime, EndDateTime, ReturnCode, Environment, ProcessType, BackupType, SQLJobName, BackupStatus, Complete, SessionID
FROM @resultstable2
OPEN ResultsTable2cursor
FETCH NEXT FROM ResultsTable2cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM ResultsTable2cursor
END
CLOSE ResultsTable2cursor
DEALLOCATE ResultsTable2cursor
WAITFOR DELAY '00:00:10'