>>>>Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. >>>>>>>>Where do I need to add the 'WITH'?
>>>TEXT TO lcSQL NOSHOW >>>IF EXISTS(SELECT * FROM sys.indexes WHERE OBJECT_name(object_id) ='MyTable' AND Name = 'IX_CATEGORY_UNIQUE') >>> BEGIN >>> EXEC sp_executesql N'DROP INDEX IX_CATEGORY_UNIQUE ON dbo.MyTable' >>> EXEC sp_executesql N'CREATE UNIQUE NONCLUSTERED INDEX [IX_CATEGORY_UNIQUENEW] ON dbo.MyTable >>> ([SITE_NO],[CATEGORY])WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, >>> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' >>> END >>>ENDTEXT >>>sqlexec.... >>>>>
>>An explicit DROP INDEX is not allowed on index 'dbo.MyTable.IX_CATEGORY_UNIQUE'. It is being used for UNIQUE KEY constraint enforcement. >>>>I think the problem is that this IX_CATEGORY_UNIQUE is both an index and is a unique key constraint. Does it mean that I have to DROP the constraint first, before dropping the index?