Lawrence-136504 (3/18/2010)

---

Which versions have you tested this logic on? I'm getting below error.

Msg 8114, Level 16, State 1, Procedure xp_sqlagent_enum_jobs, Line 1
Error converting data type nvarchar to int.

<snip>
INSERT INTO @JobState
EXEC [master].dbo.xp_sqlagent_enum_jobs @IsSysAdmin, @JobOwner, @JobID

--Paste this line into your script
PRINT 'EXEC [master].dbo.xp_sqlagent_enum_jobs ' + CAST(@IsSysAdmin AS VARCHAR(38)) + ', ''' + @JobOwner + ''', ''' + CAST(@JobID AS VARCHAR(36)) + ''''

--Normalize VARCHAR string for time formatting
</snip>

Lawrence-136504 (3/18/2010)

---

I re-copied the logic and then pasted it into ssms (query window) and removed some hidden characters and I'm getting below error now.

EXEC [master].dbo.xp_sqlagent_enum_jobs 1, 'DomainName\lawrence', 'B508F33F-8C69-42B9-9635-4CA66C05548D'


Msg 241, Level 16, State 1, Line 56
Conversion failed when converting date and/or time from character string.

Looks like it's bombing out on the DATEDIFF logic. Thoughts.

Lawrence-136504 (3/18/2010)

---

Yes. I was manually starting this job so there was no schedule.

I created a schedule that runs every 1 minute. This job has one step that has WAITFOR DELAY '00:05:00' so it runs for 5 minutes. I'm now seeing the operator email.

I'm currently using sysprocesses and sysjobs to capture long running jobs and it has been great but I'm finding that certain sql jobs that use Type: Operating system (CmdExec) and kicks off a console app (workflow), the job hangs and my current process does not capture this issue.

I'm going to add this process and see how it goes. I'll keep you posted in a couple of weeks. Thks!