Why the SQL script can run in the local environment but can not run in the other server

Question

I write a SQL script to map the name to report name. I have verify in my local SQL server 2012 machine. it works well. however, when I run in the sever, I got error

Msg 537, Level 16, State 3, Line 40
Invalid length parameter passed to the LEFT or SUBSTRING function.

and I also tried to run the "select" part directly in the remote server and result is correct too. I don't understand why it is error. have any idea? (notes: I add "error here-->" in the error line)

DECLARE @jobTable TABLE 
(
  job_name nvarchar(max),
  report_name nvarchar(max),
  event_type nvarchar(max),
  description nvarchar(max),
  last_run_date int,
  last_run_time int
)

declare
  @job_name uniqueidentifier,
  @report_name nvarchar(max),
  @event_type nvarchar(max),
  @description nvarchar(max),
  @last_run_date int,
  @last_run_time int,
  @last_run_dur int



/************** alert ********/
declare getId_alertjob_cursor cursor for (
     SELECT 
       [jobs].[name] job_name,
       alertFeed.relativePath report_name,
         CONVERT(nvarchar(max), SUBSTRING([steps].[command], CHARINDEX(' @EventType=''', [steps].[command], 0) + LEN(' @EventType='''), CHARINDEX('''', [steps].[command], CHARINDEX(' @EventType=''', [steps].[command]) + LEN(' @EventType=''')) - CHARINDEX(' @EventType=''', [steps].[command], 0) - LEN(' @EventType=''')), 0) event_type,
       [steps].[last_run_date] last_date,
       [steps].[last_run_time] last_time,
       [steps].[last_run_duration] last_dur
       FROM [msdb].[dbo].[sysjobs] [jobs] INNER JOIN [msdb].[dbo].[sysjobsteps] [steps] ON [jobs].[job_id] = [steps].[job_id], ReportingService_4c0ed75e1e8c4e50acbf853867f071f3_Alerting.dbo.AlertDefinition [alertDef], ReportingService_4c0ed75e1e8c4e50acbf853867f071f3_Alerting.dbo.Feed [alertFeed]
       WITH (NOLOCK)
       WHERE [jobs].[name] <> 'syspolicy_purge_history' 
       and alertDef.scheduleid = CONVERT(nvarchar(max), SUBSTRING([steps].[command], CHARINDEX(' @EventData=''', [steps].[command], 0) + LEN(' @EventData='''), CHARINDEX('''', [steps].[command], CHARINDEX(' @EventData=''', [steps].[command]) + LEN(' @EventData=''')) - CHARINDEX(' @EventData=''', [steps].[command], 0) - LEN(' @EventData=''')), 0)
       and alertDef.FeedId = alertFeed.FeedId
);

open getId_alertjob_cursor 
error here-->    fetch next from getId_alertjob_cursor INTO   @job_name,   @report_name,  @event_type,  @last_run_date,  @last_run_time, @last_run_dur

while @@FETCH_STATUS = 0
BEGIN
--     INSERT INTO @jobTable VALUES (@job_name, @report_name, @event_type,'Alert',0,0);
--   ,@last_run_date,@last_run_time);
     fetch next from getId_alertjob_cursor INTO   @job_name,   @report_name,  @event_type,  @last_run_date,  @last_run_time,@last_run_dur
END

close getId_alertjob_cursor
deallocate getId_alertjob_cursor

Answer 1

First of all, I would like to mention that there is no need to use a cursor in this case. I.e. your query can be simplified to the following:

DECLARE @jobTable TABLE 
(
      job_name SYSNAME
    , report_name SYSNAME
    , event_type NVARCHAR(1000)
    , [description] NVARCHAR(2000)
    , last_run_date INT
    , last_run_time INT
)

INSERT INTO @jobTable 
(
      job_name
    , report_name
    , event_type
    , last_run_date
    , last_run_time
)
SELECT 
      d.job_name
    , report_name = alertFeed.relativePath
    , d.event_type
    , d.last_date
    , d.last_time
FROM (
    SELECT 
          job_name = j.[name] 
        , event_type = CASE WHEN CHARINDEX(' @EventType=''', s.[command]) > 0 
            THEN CONVERT(NVARCHAR(MAX), SUBSTRING(s.[command], CHARINDEX(' @EventType=''', s.[command], 0) + LEN(' @EventType='''), CHARINDEX('''', s.[command], CHARINDEX(' @EventType=''', s.[command]) + LEN(' @EventType=''')) - CHARINDEX(' @EventType=''', s.[command], 0) - LEN(' @EventType=''')), 0) 
            ELSE ''
          END
        , last_date = s.[last_run_date] 
        , last_time = s.[last_run_time] 
    FROM [msdb].dbo.sysjobs j WITH (NOLOCK)
    JOIN [msdb].dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id 
    WHERE j.[name] != 'syspolicy_purge_history' 
) d
JOIN [ReportingService_4c0ed75e1e8c4e50acbf853867f071f3_Alerting].dbo.AlertDefinition [alertDef] ON alertDef.scheduleid = event_type
JOIN [ReportingService_4c0ed75e1e8c4e50acbf853867f071f3_Alerting].dbo.Feed [alertFeed] ON alertDef.FeedId = alertFeed.FeedId

Also, I'm disconcerted by the database [ReportingService_..._Alerting]. If it's located on the same server where the task is being executed, probably, it is worth to refuse to use the database.

Answer 2

I suspect it is because there are jobs with steps that don't contain the phrase @EventType=' and this is causing your substring to fail.

Add a quick check in to make sure it exists first, something like this:

Case 
        When CHARINDEX(' @EventType=''', [steps].[command]) > 0 Then
        CONVERT(nvarchar(max), SUBSTRING([steps].[command], CHARINDEX(' @EventType=''', [steps].[command], 0) + LEN(' @EventType='''), CHARINDEX('''', [steps].[command], CHARINDEX(' @EventType=''', [steps].[command]) + LEN(' @EventType=''')) - CHARINDEX(' @EventType=''', [steps].[command], 0) - LEN(' @EventType=''')), 0) 
        Else ''
        End as [EventType]

Just noticed you have this in your WHERE clause:

and alertDef.scheduleid = CONVERT(nvarchar(max), SUBSTRING([steps].[command], CHARINDEX(' @EventData=''', [steps].[command], 0) + LEN(' @EventData='''), CHARINDEX('''', [steps].[command], CHARINDEX(' @EventData=''', [steps].[command]) + LEN(' @EventData=''')) - CHARINDEX(' @EventData=''', [steps].[command], 0) - LEN(' @EventData=''')), 0)

You should also add a check to see if the command field contains @EventData

Maybe if you try to explain what you are trying to acheive someone may post a better query.