Collation conflict in DBMaint.out

Products

• Autosys Workload Automation

Issue/Introduction

DBMaint.out file showing following errors and as result nothing is archived.

CAUAJM_I_60014 Archiving Job Runs...
CAUAJM_E_18802 Error from SQLExecute() Failed with SQL_ERROR.
CAUAJM_E_18601 SQLSTATE: 42000, Native error: 468, Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
CAUAJM_E_18607 Event Server: <hostname1.domain1.com1,1433:AEDB>  Failed Stored Procedure: <{? = CALL ujo_cleanup_jobruns (?, ?, ?)} <<1698715811,50000,1>>>
CAUAJM_E_18802 Error from SQLExecute() Failed with SQL_ERROR.

Environment

Autosys r12.x - SQL Server

Cause

The default collation for the AEDB database is 'SQL_Latin1_General_CP1_CI_AS'  which is different from the collation for the ujo_temp_jobruns table.
This table is used internally in a stored procedure, only when DBMaint is run. 

 

Resolution

Execute following SQL statement to change the collation for the table:

 

TRUNCATE TABLE ujo_temp_jobruns

ALTER TABLE ujo_temp_jobruns DROP CONSTRAINT xpkujo_temp_jobruns

DROP TABLE ujo_temp_jobruns
 

CREATE TABLE ujo_temp_jobruns (
joid    integer NOT NULL,
run_num    integer NOT NULL,
ntry    integer NOT NULL,
run_machine    varchar(80) NOT NULL
)

GRANT SELECT on ujo_temp_jobruns to ujoadmin
GRANT DELETE on ujo_temp_jobruns to ujoadmin
GRANT UPDATE on ujo_temp_jobruns to ujoadmin
GRANT INSERT on ujo_temp_jobruns to ujoadmin

ALTER TABLE ujo_temp_jobruns
ADD CONSTRAINT xpkujo_temp_jobruns PRIMARY KEY CLUSTERED (run_num ASC, ntry ASC, joid ASC, run_machine ASC)