Bizarre Index on Table causing problems

Hello,
One of my sql tables has a bizarre index on it that looks like its name is
?8 (that's some weird small k, 8, and what appears to be a newline
character). The first character appears to be some sort of ascii code for
what I have no idea. The index appeared out of nowhere, and is a clustered,
hypothetical, auto create located on PRIMARY. It does not show in
Enterprise Manager's list of indexes. I believe it is the source of some
major problems on the table, and it cannot be dropped via DROP INDEX --
Cannot drop the index 'tblTable.?8', because it does not exist in the system
catalog.
Does anyone know of a way to remove this index, and where it came from? I
tried creating some sql to try and get around it
declare @.indexname nvarchar(2000)
select @.indexname = 'tblTable.' + name from sysindexes where id = 1110347070
and indid = 13
select @.indexname
declare @.sql nvarchar(3000)
set @.sql = 'DROP INDEX ' + @.indexname
exec (@.sql)
thinking that perhaps that might do the trick. No dice. If anyone can help
me out here I would really appreciate it. Thanks so much.I had something somewhat similar to this and I had to script a temp table out how I wanted, insert the data from the original to the temp, drop the original, rename the temp.
>--Original Message--
>Hello,
>One of my sql tables has a bizarre index on it that looks like its name is
>?8=04 (that's some weird small k, 8, and what appears to be a newline
>character). The first character appears to be some sort of ascii code for
>what I have no idea. The index appeared out of nowhere, and is a clustered,
>hypothetical, auto create located on PRIMARY. It does not show in
>Enterprise Manager's list of indexes. I believe it is the source of some
>major problems on the table, and it cannot be dropped via DROP INDEX --
>Cannot drop the index 'tblTable.?8', because it does not exist in the system
>catalog.
>Does anyone know of a way to remove this index, and where it came from? I
>tried creating some sql to try and get around it
>declare @.indexname nvarchar(2000)
>select @.indexname =3D 'tblTable.' + name from sysindexes where id =3D 1110347070
>and indid =3D 13
>select @.indexname
>declare @.sql nvarchar(3000)
>set @.sql =3D 'DROP INDEX ' + @.indexname
>exec (@.sql)
>thinking that perhaps that might do the trick. No dice. If anyone can help
>me out here I would really appreciate it. Thanks so much.
>
>
>.
>|||To reference names that don't conform to the rules for identifiers, try
enclosing the names in brackets (or double quotes):
DECLARE @.indexname nvarchar(261)
SELECT @.indexname = QUOTENAME(OBJECT_NAME(id)) +
'.' +
QUOTENAME(name)
FROM sysindexes
WHERE id = 1110347070 AND
indid = 13
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tglenist" <tglenist@.hotmail.com> wrote in message
news:OT5jysNwDHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> One of my sql tables has a bizarre index on it that looks like its name is
> ?8 (that's some weird small k, 8, and what appears to be a newline
> character). The first character appears to be some sort of ascii code for
> what I have no idea. The index appeared out of nowhere, and is a
clustered,
> hypothetical, auto create located on PRIMARY. It does not show in
> Enterprise Manager's list of indexes. I believe it is the source of some
> major problems on the table, and it cannot be dropped via DROP INDEX --
> Cannot drop the index 'tblTable.?8', because it does not exist in the
system
> catalog.
> Does anyone know of a way to remove this index, and where it came from? I
> tried creating some sql to try and get around it
> declare @.indexname nvarchar(2000)
> select @.indexname = 'tblTable.' + name from sysindexes where id =1110347070
> and indid = 13
> select @.indexname
> declare @.sql nvarchar(3000)
> set @.sql = 'DROP INDEX ' + @.indexname
> exec (@.sql)
> thinking that perhaps that might do the trick. No dice. If anyone can
help
> me out here I would really appreciate it. Thanks so much.
>
>|||"tglenist" <tglenist@.hotmail.com> wrote:
>One of my sql tables has a bizarre index on it that looks like its name is
>?8 (that's some weird small k, 8, and what appears to be a newline
>character). The first character appears to be some sort of ascii code for
>what I have no idea. The index appeared out of nowhere, and is a clustered,
>hypothetical, auto create located on PRIMARY. It does not show in
>Enterprise Manager's list of indexes. I believe it is the source of some
>major problems on the table, and it cannot be dropped via DROP INDEX --
>Cannot drop the index 'tblTable.?8', because it does not exist in the system
>catalog.
>Does anyone know of a way to remove this index, and where it came from? I
>tried creating some sql to try and get around it
>declare @.indexname nvarchar(2000)
>select @.indexname = 'tblTable.' + name from sysindexes where id = 1110347070
>and indid = 13
>select @.indexname
>declare @.sql nvarchar(3000)
>set @.sql = 'DROP INDEX ' + @.indexname
>exec (@.sql)
>thinking that perhaps that might do the trick. No dice. If anyone can help
>me out here I would really appreciate it. Thanks so much.
This sounds related to a recent thread entitled "Hypothetical indexes",
F8EE1587-3A79-4E3A-85B8-BE0B5B2E3A59@.microsoft.com
It refers to a KB article,
http://support.microsoft.com/support/kb/articles/Q293/1/77.ASP
HTH,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander