ALTER   PROCEDURE GetCurrentJobInfo AS

SELECT   p.SPID,
	Blocked_By = p.Blocked,
	p.Status,
	p.LogiName,
	p.HostName,
	Program = coalesce('Job: ' + j.name, p.program_name),
	p.program_name,
	job_name = coalesce(j.[name], ''),
	jobstep_id = coalesce(js.[step_id], ''),
	jobstep_name = coalesce(js.[step_name], ''),
             js.[command],
	dts_name = coalesce(d.name, ''),
	DBName = db_name(p.dbid), 
	Command = p.cmd,
	CPUTime = p.cpu,
	DiskIO = p.physical_io,
	LastBatch = p.Last_Batch,
--	LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ),  -- SQL Server 2005+
--	LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ),  -- SQL Server 2000 ? FAILS
	p.WaitTime,   
	p.LastWaitType,   
	LoginTime = p.Login_Time,   
	RunDate = GetDate(),
	[Server] = serverproperty('machinename'),   
	[Duration(s)] = datediff(second, p.last_batch, getdate())  
FROM master.dbo.sysprocesses p  
	left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100) 
	left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id 
		and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 ) 
	left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')
where p.spid > 50  
--	and p.status <> 'sleeping' 
	and p.spid <> @@spid  
order by p.spid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------
-- 
-- ============================================
-- JOB STEP 1: Update 'hist' tables (job steps 2 & 3 are similar)
-- ============================================
 
-- Initially set errcode to 0 for ALL steps
update [scheduler] set [errcode] = 0

declare @result int

exec @result = master.dbo.xp_cmdshell 'C:\some.exe C:\someddb.mdb'
update [Scheduler] set [errcode] = @result, [value] = 0 where [key] = 'Upload hist data'

if (@result = 0)
   print 'Success'
else
begin
   print 'Failure: error code=' + CONVERT( char(8), @result )
   RAISERROR('Upload hist data failed.', 11, 1)
end
----------------------------------------
-- 
-- ============================================
-- JOB STEP 4: Refresh 24hr Cubes  (every 10-20mins)
-- ============================================
 
declare @result int
 
exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
update Scheduler set [errcode] = @result, [value] = 0 where [key] = 'Refresh 24hr Cubes'

if (@result = 0)
   print 'Success'
else
begin
   print 'Failure: error code=' + CONVERT( char(8), @result )
   RAISERROR('24hr Cubes failed to refresh.', 11, 1)
end

----------------------------------------
-- 
-- ============================================
-- JOB STEP 5: FULL Process 24hr Cubes IF required  (1am)
-- ============================================
 
declare @result int
declare @processcubes int
 
set @processcubes = ( select [value] from Scheduler where [key] = 'FULL Process 24hr Cubes' )

if @processcubes = 1
begin
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update Scheduler set [errcode] = @result where [key] = 'FULL Process 24hr Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update [Scheduler] set [value] = 0 where [key] = 'FULL Process 24hr Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('24hr Cubes failed to FULL process.', 11, 1)
  end
end
else  
  print '24hr Cubes not scheduled to be FULL processed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 6: Refresh Cubes IF required  (6:35am/pm)
-- ============================================
 
declare @result int
declare @refreshcubes int
 
set @refreshcubes = ( select [value] from Scheduler where [key] = 'Refresh Cubes' )
 
if @refreshcubes = 1
begin
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update Scheduler set [errcode] = @result where [key] = 'Refresh Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update [Scheduler] set [value] = 0 where [key] = 'Refresh Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('Cubes failed to refresh.', 11, 1)
  end
end
else  
  print 'Cubes not scheduled to be refreshed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 7: FULL Process Cubes IF required  (1am)
-- ============================================
-- Select 'tempdb' database for this job so SHRINKfile commands work on that database.
-- Note: hardcoded 'thedb..Scheduler' used as 'tempdb' selected.
 
declare @result int
declare @processcubes int
 
set @processcubes = ( select [value] from thedb..Scheduler where [key] = 'FULL Process Cubes' )
 
if @processcubes = 1
begin
  print 'Shrink tempdb - tempdev: ' + RIGHT( GETDATE(),7 )
  DBCC SHRINKfile ('tempdev') WITH NO_INFOMSGS
  print 'Shrink tempdb - templog: ' + RIGHT( GETDATE(),7 )
  DBCC SHRINKfile ('templog') WITH NO_INFOMSGS
  
  print 'FULL process cubes: ' + RIGHT( GETDATE(),7 )
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update thedb..Scheduler set [errcode] = @result where [key] = 'FULL Process Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update thedb..Scheduler set [value] = 0 where [key] = 'FULL Process Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('Cubes failed to FULL process.', 11, 1)
  end
end
else  
  print 'Cubes not scheduled to be FULL processed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 8: Reindex Tables IF required  (SUN 12am)
-- ============================================

declare @result int  
declare @reindextables int

set @result = 0
set @reindextables = ( select [value] from Scheduler where [key] = 'Reindex Tables' )
 
if @reindextables = 1
begin
  -- for now, turn flags off as assume job will SUCCEED - if FAILS, turn flag back on
  update Scheduler set [errcode] = 0, [value] = 0 where [key] = 'Reindex Tables'

  DECLARE @TableName varchar(255)
  DECLARE TableCursor CURSOR FOR
	SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

  OPEN TableCursor
  FETCH NEXT FROM TableCursor INTO @TableName
  WHILE @@FETCH_STATUS = 0
  BEGIN 
    IF LEFT(@TableName,5) = 'hist_'
       PRINT @TableName + ': ' + RIGHT( GETDATE(),7 )
    DBCC DBREINDEX(@TableName, ' ', 100) WITH NO_INFOMSGS
    SELECT @result = @@error

    -- DBCC caused an error, set errcode (if it's < @result)
    IF @result <> 0
    BEGIN
      print 'Failure: error code=' + CONVERT( char(8), @result ) + @TableName
      -- turn flag back on as FAILED
      update Scheduler set [value] = 1 where [key] = 'Reindex Tables'
      -- set errcode to highest failure error code returned
      update Scheduler set [errcode] = @result where [key] = 'Reindex Tables' and [errcode] < @result
    END

    FETCH NEXT FROM TableCursor INTO @TableName

  END
  CLOSE TableCursor
  DEALLOCATE TableCursor

end
else  
  print 'Tables not scheduled to be reindexed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 9: Return Success / Error Code
-- ============================================

declare @worst_result int
declare @error_count varchar(2)
declare @step_count varchar(2)

set @worst_result = (select max(errcode) from Scheduler)
set @error_count = cast((select count(*) from Scheduler where errcode > 0) as varchar(2))
set @step_count = cast((select count(*) from Scheduler) as varchar(2))

if @worst_result > 0 
begin
  PRINT 'OVERALL FAILURE:  PowerView Upload Job had ' + @error_count + ' failed step(s) out of ' + @step_count + '!'
  RAISERROR('Upload Job had failed step(s)!', 11, 1)
  -- syntax error near +
  -- RAISERROR('Upload Job had ' + @error_count + ' failed step(s)!', 11, 1)
end
else
   PRINT 'OVERALL SUCCESS: ALL steps completed successfully.'
----------------------------------------

KILL 54;
KILL 54 WITH STATUSONLY;
GO

SELECT [name], starttime, endtime, datediff(ss, starttime, getdate() )/3600.0 as DTS_RUNHRS
FROM sysdtspackagelog 
where endtime is NULL
 --and datediff(ss, starttime, getdate() )/3600.0 >= 4
ORDER BY starttime DESC

select spid, kpid, dbid, status, cpu, cpu/3600.0 as RUNHRS, program_name, login_time, cmd, sql_handle
from master.dbo.sysprocesses
where spid>=50 and dbid>0
--	and cpu >= (3600)
and [program_name] = 'Microsoft SQL Server Analysis Services'
order by spid

        left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id 
                --and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 ) 

        job_step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 ) 

DECLARE @a varchar(50)
SET @a = '1234567890'
SELECT SUBSTRING(@a,5,LEN(@a))		-- This one is OK, normal substring.
SELECT SUBSTRING(@a,5,LEN(@a)-5)	-- This one is OK, your length is still 5 (10-5)
SELECT SUBSTRING(@a,15,LEN(@a)-5)	-- This one is OK, you can have a starting point past the end of the string.
SELECT SUBSTRING(@a,5,LEN(@a)-11)	-- This one fails because you have a length of -1

LEFT OUTER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id 
                and js.step_id = CASE WHEN LEN(p.program_name) >72 THEN SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )
										ELSE NULL -- This will always be false, nothing equals NULL.  You could also just use p.program_name here.  Not sure what the end goal is
										END

-- NOTE: dts_name will ONLY return a value 'IF' DTSRUN used with dtsname.
SELECT  
	p.SPID,
        Blocked_By = p.Blocked,
        p.Status,
        p.LogiName,
        p.HostName,
	p.open_tran,
        Program = coalesce('Job: ' + j.[name], p.program_name),
        p.program_name,
        job_name = coalesce(j.[name], ''),
        jobstep_id = coalesce(js.[step_id], ''),
        jobstep_name = coalesce(js.[step_name], ''),
        js.[command],
        dts_name = coalesce(d.[name], ''),
        DBName = db_name(p.dbid), 
        Command = p.cmd,
        CPUTime = p.cpu,
        DiskIO = p.physical_io,
        LastBatch = p.Last_Batch,
        -- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+
        -- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS
        p.WaitTime,   
        p.LastWaitType,   
        LoginTime = p.Login_Time,   
        RunDate = GetDate(),
        [Server] = serverproperty('machinename'),   
        [Duration] = datediff(second, p.last_batch, getdate())  
FROM master.dbo.sysprocesses p  
        left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)
        left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 ) 
        left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')
where 
	p.spid > 50  
--      and p.status <> 'sleeping' 
        and p.spid <> @@spid  
order by 
	p.spid