Showing posts with label weird. Show all posts
Showing posts with label weird. Show all posts

Sunday, February 12, 2012

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