topic Re: Cannot drop view from sde, only option is commandline in Geodatabase Questions

Cannot drop view from sde, only option is commandline

JeffPace — Wed, 04 Feb 2015 15:06:12 GMT

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!

Re: Cannot drop view from sde, only option is commandline

DavidColey — Wed, 04 Feb 2015 23:23:47 GMT

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....

Re: Cannot drop view from sde, only option is commandline

VinceAngelo — Thu, 05 Feb 2015 03:38:02 GMT

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

Re: Cannot drop view from sde, only option is commandline

JeffPace — Thu, 05 Feb 2015 17:40:30 GMT

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.