Migration to 24 final sql error Cannot alter sequence because it does not exist...
Article ID: 430992
Updated On:
Products
Issue/Introduction
When migrating to version 24, the final sql statement throw an error like:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the sequence 'SQ_MQ1AUT', because it does not exist or you do not have permission.
Completion time: YYYY-MM-DDTHH:MM:SS.8413617-04:00
It could happen with other sequences as well
Environment
AE: V24
Cause
This is caused by the sequence not being defined in the destination database.
Resolution
Investigation
Anything that was in the source database should have been moved to the destination database as part of the initial run of the migration workflow. The SQ_MQ* sequences should have been moved during the run of PCK.AUTOMIC_UTF-8.MIG-STEP04.CREATE.TABLES.MQ.DST which uses the variable, PCK.AUTOMIC_UTF-8.MIG.SQLI.STEP08.CREATE.SEQUENCES.MQ.SRC which runs the SQL below on the source MQ database:
select creseq_stmt from dbo.UCMIG_SEQUENCES
and then loops through all the results to create the same sequences in the destination MQ tables.
Check the the report for PCK.AUTOMIC_UTF-8.MIG-STEP04.CREATE.TABLES.MQ.DST; there should be a line like:
YYYY-MM-DD HH:MM:SS CREATE SEQUENCE [dbo].[SQ_MQ1AUT] AS int START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 CYCLE CACHE 1000
This job should create the sequence in the destination database. If there are no errors in the job report, you will want to run the statement manually on the destination MQ database and have a DBA involved to see why it doesn't show up in the destination database even though it is in the source database.
Immediate resolution
Have a DBA look at the CREATE SEQUENCE... statement from the job above. This should be run against the database. Another option is to have them look at the CREATE SEQUENCE SQ_MQ* statements in the Automic image's /db/sql_9/24.4/mqdb_dll.sql file and create the sequences based off of that.
Feedback
