Apologies in advance if this is already known, I didn't read through the whole thread. The issue is likely arising because one of your strings is < 72 characters, resulting in a negative substring length. Use a case statement to check that before you do your substring.

Here's an example of why it fails:

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

Here's an example of a fix:

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