Index already exists - EM bug?
-
I am trying to create an index in Enterprise Manager, but I am getting this message:
Index 'IX_CreatedBy_User' already exists.
The Help says:
An index already exists for table '<0s>' with the columns '<1s>'.
This index does _not_ exist in the table to which I am adding it, at least according to EM. In an attempt to view indexes as stored, I ran 'select * from sysindexkeys' in Query Analyzer, but I don't know how to read the 93 rows that it returns. I don't see any of the labels I assigned.
I have 37 tables and each has a uniqueidentifier-type field named 'GID_CreatedBy_User'.
I deleted all indexes named 'IX_CreatedBy_User' in all files. Then I was able to add the index to 2 tables, but I received the same error in the 3rd table.
I experienced the same problem earlier while adding indexes on another field, which is varchar(4) type. I started getting this message after creating that index in about 20 tables.
Is this an EM bug? Any help will be appreciated.
-
Hey!!! Go through this one
USE <Database_Name>
SET NOCOUNT ON
GO
/*
** If the objects already exist (i.e. if this is a rebuild), drop them.
*/
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_CreatedBy_User' )
DROP INDEX Table.IX_CreatedBy_User
GO
/* Again create Index*/
/* Create the Index with a FILLFACTOR of 80 */
CREATE <NONCLUSTERED/CLUSTERED> INDEX IX_CreatedBy_User ON Table_Name(Column_Name)
WITH FILLFACTOR = 80
GO
SET NOCOUNT OFF
GO
Shashank
Regards,
Papillon
-
Mario,
Make sure you don't have table names which are duplicated. By this, I am saying if a user logs in with his windows authenticated username and he is dbo, he or another person can create a table with the same name as another username. For example, you can have two tables with the name of "Products" in your database if they are created by different users. It would look like this: jsmith.dbo.products and jjohnson.dbo.products
If this was the case, you could end up with duplicated index names.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator - "It would look like this: jsmith.dbo.products and jjohnson.dbo.products"Based on the standard naming convention database.owner.object I believe it is more like: database.jsmith.products and database.jjohnson.products
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Yes Rudy, you are correct!
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator -
This is the dreaded Enterprise Manager refresh problem is it?
--------------------
Colt 45 - the original point and click interface
-
Thank you all for your help. I ended up putting the file name in index names, which made them all uniquely named. This worked. I will have someone with SQL Server experience check the schema before proceeding with it.
-
Looks like it was.
-
One thing you get to know pretty quickly if you use EM for your development tool. Refresh, Refresh, Refresh and once more Refresh.
--------------------
Colt 45 - the original point and click interface
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply