SQLAGENT job fails with "String index out of range: 0"

Document ID : KB000009141
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

 

We have the following SQLAGENT job defined to execute MS SQL Server Agent job named test_sql_agent_plugin.

 

/* ----------------- test_SQL_plugin ----------------- */

insert_job: test_SQL_plugin   job_type: SQLAGENT
machine: sqlagent-I3687
owner: Administrator@sqlagent-I3687
permission:
date_conditions: 0
alarm_if_fail: 1
sqlagent_user_name: dbusr
sqlagent_target_db: SQLDB
sqlagent_jobname: test_sql_agent_plugin
sqlagent_server_name: sqlagent-I3687

 

When we execute the AE job, it fails immediately with the error "String index out of range: 0".

[01/24/2018 15:26:49]     CAUAJM_I_40245 EVENT: STARTJOB         JOB: test_SQL_plugin
[01/24/2018 15:26:49]     CAUAJM_I_40245 EVENT: CHANGE_STATUS    STATUS: STARTING        JOB: test_SQL_plugin MACHINE: sqlagent-I3687
[01/24/2018 15:26:49]     CAUAJM_I_10082 [sqlagent-I3687 connected for test_SQL_plugin 109.11598.1]
[01/24/2018 15:26:50]     CAUAJM_I_40245 EVENT: CHANGE_STATUS    STATUS: RUNNING         JOB: test_SQL_plugin MACHINE: sqlagent-I3687
[01/24/2018 15:26:50]     CAUAJM_I_40245 EVENT: CHANGE_STATUS    STATUS: FAILURE         JOB: test_SQL_plugin MACHINE: sqlagent-I3687
EXITCODE:  1
[01/24/2018 15:26:50]     <String index out of range: 0>
[01/24/2018 15:26:51]     CAUAJM_I_40245 EVENT: ALARM            ALARM: JOBFAILURE       JOB: test_SQL_plugin MACHINE: sqlagent-I3687
EXITCODE:  1

 

We are however able to run the SQL Server Agent job successfully as "dbusr" database user directly from Microsoft SQL Management Studio. 

 

Environment:
CA Workload Automation AE 11.3.6 SP6CA WA Agent for Windows with CA WA Agent for Microsoft SQL Server (plugin).
Cause:

 

The cause of the String index out of range: 0 error is the incomplete or incorrect specification of the database user name for job attribute sqlagent_user_name in the job definition.

insert_job: test_SQL_plugin   job_type: SQLAGENT
machine: sqlagent-I3687
owner: Administrator@sqlagent-I3687
permission:
date_conditions: 0
alarm_if_fail: 1
sqlagent_user_name: dbusr
sqlagent_target_db: SQLDB
sqlagent_jobname: test_sql_agent_plugin
sqlagent_server_name: sqlagent-I3687

 

As stated in the documentation, the database user name must be specified in the user@token format and it must match the user@host value that was created using the autosys_secure command ([5] Manage users -> [1] Manage users with password -> [1] Create a user). 

Simply put, any mismatch between the user@token and the user@host will result in String index out of range: 0.

 

Resolution:

Create user dbusr@SQLDB using the autosys_secure command (with password for user dbusr in the database SQLDB ) and update the job definition, like so:

C:\Program Files (x86)\CA\Workload Automation AE>jil
jil>>1> update_job: test_SQL_plugin
jil>>2> sqlagent_user_name: dbusr@SQLDB
jil>>3> exit
______________________________________________________________________________

CAUAJM_I_50323 Inserting/Updating job: test_SQL_plugin
CAUAJM_I_50205 Database Change WAS Successful!
______________________________________________________________________________

CAUAJM_I_52301 Exit Code = 0
______________________________________________________________________________


C:\Program Files (x86)\CA\Workload Automation AE>autorep -J test_SQL_plugin -q


/* ----------------- test_SQL_plugin ----------------- */

insert_job: test_SQL_plugin   job_type: SQLAGENT
machine: sqlagent-I3687
owner: Administrator@sqlagent-I3687
permission:
date_conditions: 0
alarm_if_fail: 1
sqlagent_user_name: dbusr@SQLDB
sqlagent_target_db: SQLDB
sqlagent_jobname: test_sql_agent_plugin
sqlagent_server_name: sqlagent-I3687


Submit the job and confirm it runs to success:

[01/25/2018 10:32:00]     ----------------------------------------
[01/25/2018 10:32:15]     CAUAJM_I_40245 EVENT: STARTJOB         JOB: test_SQL_plugin
[01/25/2018 10:32:15]     CAUAJM_I_40245 EVENT: CHANGE_STATUS    STATUS: STARTING        JOB: test_SQL_plugin MACHINE: sqlagent-I3687
[01/25/2018 10:32:15]     CAUAJM_I_10082 [sqlagent-I3687 connected for test_SQL_plugin 109.11604.1]
[01/25/2018 10:32:16]     CAUAJM_I_40245 EVENT: STATE_CHANGE     JOB: test_SQL_plugin MACHINE: sqlagent-I3687
[01/25/2018 10:32:16]     <Submitting at SQLDB>
[01/25/2018 10:32:16]     CAUAJM_I_40245 EVENT: CHANGE_STATUS    STATUS: RUNNING         JOB: test_SQL_plugin MACHINE: sqlagent-I3687
[01/25/2018 10:32:16]     <Executing at SQLDB>
[01/25/2018 10:32:31]     CAUAJM_I_40245 EVENT: CHANGE_STATUS    STATUS: SUCCESS         JOB: test_SQL_plugin MACHINE: sqlagent-I3687
EXITCODE:  0
[01/25/2018 10:32:31]     <step 1 Succeeded; >
[01/25/2018 10:33:00]     ----------------------------------------

Additional Information: