SQLAGENT job fails with "String index out of range: 0"
Article ID: 9141
Updated On:
Products
Issue/Introduction
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-678
owner: Administrator@sqlagent-678
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-678
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-678
[01/24/2018 15:26:49] CAUAJM_I_10082 [sqlagent-678 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-678
[01/24/2018 15:26:50] CAUAJM_I_40245 EVENT: CHANGE_STATUS STATUS: FAILURE JOB: test_SQL_plugin MACHINE: sqlagent-678
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-678
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
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-678
owner: Administrator@sqlagent-678
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-678
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-678
owner: Administrator@sqlagent-678
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-678
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-678
[01/25/2018 10:32:15] CAUAJM_I_10082 [sqlagent-678 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-678
[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-678
[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-678
EXITCODE: 0
[01/25/2018 10:32:31] <step 1 Succeeded; >
[01/25/2018 10:33:00] ----------------------------------------
Feedback
