Re: Orphan Indexes?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrew (AndrewR2k1_at_hotmail.com)
Date: 08/04/04 

Date: Wed, 4 Aug 2004 16:05:47 -0700

I have tried performing the DROP as both part of and outside of the stored
procedure. I tried the DROP from Query Analyzer and from the helper
program. I have tried changing the owner of the table and then performing
the DROP in the manners described iin both the previous two sentences.

End result: no change.

I cannot get rid of these indexes. I have even tried openign the
"sysindexes" table through SQL Server Enterprise Manager, and attempting to
manually delete the records containing these indexes. I am denied access.

I am out of ideas. Nothing I try works, the indexes are like white on rice.
Help??

-- Andrew

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:u04Z9BleEHA.2028@tk2msftngp13.phx.gbl...
> Ahh, perhaps you are doing the DROP inside the proc, so the parser don't
know of the existence yet (as they
> haven't been created at parse time... or something like that. What if you
use dynamic SQL to execute the DROP
> INDEX statement?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Andrew" <AndrewR2k1@hotmail.com> wrote in message
news:uVq5dhkeEHA.2044@TK2MSFTNGP10.phx.gbl...
> > Tried this already, still have the same problem.
> >
> > drop index dbo.t.x
> > drop index (username).t.x
> >
> > Makes no difference
> >
> > -- Andrew
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> > message news:%23y5RhYkeEHA.4068@TK2MSFTNGP11.phx.gbl...
> > > Perhaps an owner issue for the table? You can specify the table owner
in
> > DROP INDEX, as below shows:
> > >
> > > create table t(c1 int)
> > > create index x on t(c1)
> > > drop index dbo.t.x
> > >
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Andrew" <AndrewR2k1@hotmail.com> wrote in message
> > news:%23xuiWBkeEHA.1732@TK2MSFTNGP09.phx.gbl...
> > > > Hey all,
> > > >
> > > > I have run into a problem and I am completely lost.. I wrote a
couple
> > > > stored procedures to be used by a little helper program to delete
and
> > remake
> > > > a table, upload some data to the DB from a text file the program
read
> > in,
> > > > and finally, apply a couple indexes to the newly made table
> > (TempPri2004).
> > > >
> > > > I ran into a snag in that when the program ran the SP's, the owner
of
> > the
> > > > table is not the same as when I created the SP's in Query Analyzer.
So,
> > I
> > > > added to the program a step that would run the SP
"sp_changeobjectowner"
> > to
> > > > change the owner so my SP's would run. But now I have a major
problem.
> > > >
> > > > I can no longer get rid of the indexes my SP created in the final
step
> > of
> > > > the process. If I run the following query, I get a record back with
the
> > > > Index name (it was found, in other words):
> > > >
> > > > "SELECT name FROM sysindexes WHERE name = 'NC_RaceLabel'"
> > > > Records returned: NC_RaceLabel
> > > >
> > > > But if I try the following, it bombs with the error given:
> > > >
> > > > "DROP INDEX TempPri2004.NC_RaceLabel"
> > > > Error: Cannot drop the index 'TempPri2004.NC_RaceLabel', because it
does
> > not
> > > > exist in the system catalog.
> > > >
> > > > If I open SQL Server Enterprise Manager, expand the database for
this
> > table,
> > > > right click the table, and select Manage Indexes, there is no items
> > listed
> > > > in the box.
> > > >
> > > > I am at a loss here. The indexes are listed in the "sysindexes"
table
> > if I
> > > > query the table, but I am unable to drop them. Help??
> > > >
> > > > -- Andrew
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Sql2k, Performance and Junction Points
    ... I have the answer to all but the "Better Performing" question which is what ... I did find a reference to configuring Siebel with Sql Server on a SAN, ... >> We have been configuring our Larger SQL Servers with the following disk ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server extremely slow
    ... An Access application using a SQL Server back-end via ODBC linked tables is ... it was performing well anyway, then there isn't much scope to improve it!) ... running any query involves TWO query engines: ... database and shunted across the network so that Access/Jet can execute the ...
    (comp.databases.ms-access)
  • Re: Sql2k, Performance and Junction Points
    ... Better performing? ... > d:\ SQL Server Executables ... > e:\ Database Files, tempdb. ... > the SQL Server configurations are the same. ...
    (microsoft.public.sqlserver.setup)
  • Re: SqlConnection and DB connection failures detection
    ... Now question - after performing insert query I'm closing connection, ... but my process still exists in SQL Server Activity Monitor (even if i ... close my app) with "Sleeping" status. ... Then second question - I'm using SQL Server authentication instead of ...
    (microsoft.public.dotnet.languages.csharp)
  • How to monitor insert?
    ... When I am performing an Insert into a table using Query Analyzer, ... not sure how to find information in Performance Monitor or SQL Profiler. ...
    (microsoft.public.data.ado)