SQL Upgrade Troubleshooting in the script 5.2.4.9to5.2.4.10.sql

Owned by Agat Support
Last updated: Apr 14, 2022 by Avi J. Levin (Unlicensed)
1 min read

When we try to upgrade we might encounter the following:

 

Msg 5074, Level 16, State 1, Server <Servername>, Line 19 The index 'IX_SCA_CELL_LOGIN_DeviceUD_CommonCookieNonce' is dependent on column 'CommoncookieNonce' Msg 5074, Level 16, State 9, Server <Servername>, Line 19 ALTER TABLE ALTER COLUMN CommonCookieNonce failed because one or more objects access this column

How can I fix it?

To fix, we need to remove the constraint from the table do the upgrade and then reinsert the constraint.

This can be done by:

  1. executing the following query:

    DROP INDEX SCA_CELL_LOGIN.IX_SCA_CELL_LOGIN_DeviceUD_CommonCookieNonce;

  2. Run the SQL scripts from the point of failure <path-to-installation-files>\Sphereshield.Setup\Payload\SqlScripts\Upgrade\ post the issue(i.e. “5.2.4.9to5.2.4.10.sql” or any other posterior version

  3. Execute the following query to reinstate the index

    CREATE NONCLUSTERED INDEX IX_SCA_CELL_LOGIN_DeviceUD_CommonCookieNonce ON dbo.SCA_CELL_LOGIN (DeviceUC) INCLUDE([CommonCookieNonce]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]