TMS upgrade error code 1902

[Bob Fitzgerald]

Hello!

Running into an issue with a TMS14.2.x upgrade.  The upgrade keeps bombing out when updating the database.  Originally it was from having null values in PlannedEndTime.  After running the script to fix that the customer is running into a new error;

Error Code 1902 at line 1: Cannot create more than one clustered index on table 'dbo.AclUser'. Drop the existing clustered index 'ix_clust_temp' before creating another.

Is it simply a matter of deleting the existing 'ix_clust_temp' and running the installer again? 

Thanks!

[Bob Fitzgerald]

Hello again!

Since I am unversed in SQL would someone have the queries to locate and/or delete 'ix_clust_temp'?

Thanks!

[Zac Colton · Accepted solution]

I believe I have ran into this before. What I did was put a SQL query together to clear out the statistics so that they can be rebuilt correctly. Like any other action that does a delete. Be sure to get a solid backup of the database before anything like this is attempted.

DECLARE @ObjectName sysname

DECLARE @StatsName sysname

DECLARE StatsCursor CURSOR FAST_FORWARD

FOR

SELECT

'['+OBJECT_NAME(object_id)+']' as 'ObjectName',

'['+[name]+']' as 'StatsName'

FROM sys.stats

WHERE

(INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1

OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)

AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

OPEN StatsCursor

FETCH NEXT FROM StatsCursor

INTO @ObjectName, @StatsName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)

FETCH NEXT FROM StatsCursor

INTO @ObjectName, @StatsName

END

CLOSE StatsCursor

DEALLOCATE StatsCursor DECLARE @ObjectName sysname
DECLARE @StatsName sysname
DECLARE StatsCursor CURSOR FAST_FORWARD
FOR
SELECT
'['+OBJECT_NAME(object_id)+']' as 'ObjectName',
'['+[name]+']' as 'StatsName'
FROM sys.stats
WHERE
(INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1
OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
OPEN StatsCursor
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
END
CLOSE StatsCursor
DEALLOCATE StatsCursor

[Bob Fitzgerald]

Hey there Zac!

Thanks for this.  We'll try this out and I'll let you know how it went.

Have a great weekend!

[Bob Fitzgerald]

Hi Zac,

That did the trick.  Thank you very much for the info.