Cannot drop view from sde, only option is commandline

3942
3
02-04-2015 07:06 AM
Highlighted
MVP Regular Contributor

So this is the second time this week I have had to use the commandline.  We have a spatial view where the schema changed of one of the source tables.The view was registered with SDE at an earlier version (10.0)

This caused SDE to loose track of the view.

1. View is not in the list in arccatalog. 

2. View was dropped and recreated in sql with no affect

3. Cannot create a new view in arccatalog as "a table with that name already exists"

4. sdetable -o describe Thinks the table still exists

If I drop the table with sdetable -o delete I can recreate the view in sql and all is well, but what happens when they take away this tool?

I cannot drop it with ArcToolbox, as I cannot browse to it to select it, since it does not show up!

Reply
0 Kudos
3 Replies
Highlighted
Regular Contributor III

You're probably not going to want to hear this, but I've had instances where even though I ran the sdetable -o delete command, I still had to go in and make sure all references to the view were also removed from all of the system tables, like GDB_ITEMS, GDB_ITEMTYPES, GDB_TABLES_LAST_MODIFIED, SDE_ table_registry etc.

Otherwise the geodatabase thought the view still existed....

Highlighted
Esri Esteemed Contributor

Best practice for making schema changes is to drop the views first.  If they've been registered with ArcSDE, then you need to use either Desktop/Python or ArcSDE command-line to remove them.  Once you've corrupted the geodatabase, there aren't any easy ways back to stability (which, ironically, is one of the main reasons the command-line tools have been removed -- folks kept hacking the SDE tables without properly managing the GDB tables).

The technique I usually use (if I don't roll back the database to the point before it was corrupted) is to create a new table with database tools using the name of the incompletely dropped table.  Then I can use Catalog to drop the table properly (though sometimes I need to double back and create the table with 'sdetable' if the SDE registry is also corrupt, then re-try the Desktop delete).  Needless to say, there are multiple backups made before I start these kinds of games. In truth, I can't think of any time since the release of 9.3 where I've needed to resolve GDB corruption by using DML against the SDE.GDB_* tables, so I haven't needed to get a Tech Support analyst on the line to help guide me.

While having the command-line tools is a benefit in this situation, it's not really the only (or best) solution. 

- V

Highlighted
MVP Regular Contributor

Vince Angelo

commandline is the only option.  even after recreating it in SQL sde (arccatalog, desktop, python) could not see it

This situation was caused by changing the schema of a table that participate in a view, which invalidated the view.  The view was then modified, SDE "lost" it.