Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

TMS upgrade error code 1902

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Cisco Employee

Re: TMS upgrade error code 1902

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

4 REPLIES
New Member

TMS upgrade error code 1902

Hello again!

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

Thanks!

Cisco Employee

Re: TMS upgrade error code 1902

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

New Member

Re: TMS upgrade error code 1902

Hey there Zac!

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

Have a great weekend!

New Member

Re: TMS upgrade error code 1902

Hi Zac,

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

361
Views
0
Helpful
4
Replies