07-26-2013 11:15 AM - edited 03-18-2019 01:31 AM
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!
Solved! Go to Solution.
07-26-2013 01:17 PM
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
07-26-2013 11:37 AM
Hello again!
Since I am unversed in SQL would someone have the queries to locate and/or delete 'ix_clust_temp'?
Thanks!
07-26-2013 01:17 PM
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
07-26-2013 02:15 PM
Hey there Zac!
Thanks for this. We'll try this out and I'll let you know how it went.
Have a great weekend!
07-30-2013 03:07 PM
Hi Zac,
That did the trick. Thank you very much for the info.
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: