Error in Web UI Forecast : "Invalid object name 'SEQ_GEN_SEQUENCE'"

Products

• Autosys Workload Automation

Issue/Introduction

The  forecast report created in Autosys WebUI returns an invalid object error: 

E210035 Unexpected error occurred when attempting to generated report '
 Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'SEQ_GEN_SEQUENCE'.
 Error code: 208
 Call: SELECT NEXT VALUE FOR SEQ_GEN_SEQUENCE
 Query: Valu...

 

 

Environment

Release : 12.1

Cause

The error message is due to missing sequence in the Microsoft SQL server database.

Resolution

1. Login to AEUIDB with sa user and execute below statements

    CREATE SEQUENCE SEQ_GEN_SEQUENCE INCREMENT BY 50 START WITH 50 ;
    ALTER TABLE dbo.FRCST_FORECAST_RESULT ADD NEW_ID INTEGER NOT NULL;
    UPDATE dbo.FRCST_FORECAST_RESULT SET NEW_ID=ID WHERE ID IN (select ID FROM FRCST_FORECAST_RESULT);

    DECLARE @SQL VARCHAR(512)
    SET @SQL = 'ALTER TABLE FRCST_FORECAST_RESULT DROP CONSTRAINT |ConstraintName| '
    SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID('FRCST_FORECAST_RESULT')))
    EXEC (@SQL)
    ;
    ALTER TABLE FRCST_FORECAST_RESULT DROP COLUMN ID;
    EXEC sp_rename 'FRCST_FORECAST_RESULT.NEW_ID', 'ID', 'COLUMN';
    ALTER TABLE FRCST_FORECAST_RESULT ADD CONSTRAINT FRCST_FORECAST_RESULT_PK PRIMARY KEY (ID);

    GRANT UPDATE, ALTER ON dbo.SEQ_GEN_SEQUENCE TO wccuser

<Note Replace the  database user name: "wccuser" from the above query with the WebUI database user being used in your environment>

2. No services restart is required. 

Reattempt/refresh the WCC Forecast window in the WebUI. The error would be disappeared.