topic DBMS table not found (-37) in Data Management Questions

DBMS table not found (-37)

AntonGonak — Tue, 04 Sep 2012 08:33:13 GMT

When I trying to create a view with the following syntax:

sdetable -o create_view -T DPSTR_GIS.CONS_SHP_LAND_V -t DPSTR_GIS.CONS_SHP_LAND,DPSTR_GIS.CONS_LAND -c DPSTR_GIS.CONS_SHP_LAND.OBJECTID,DPSTR_GIS.CONS_SHP_LAND.SHAPE,DPSTR_GIS.CONS_SHP_LAND.NSI_ID,DPSTR_GIS.CONS_LAND.CAD_NUM,DPSTR_GIS.CONS_LAND.ALLOW_USE,DPSTR_GIS.CONS_LAND.LAND_CAT,DPSTR_GIS.CONS_LAND.DEN_AREA,DPSTR_GIS.CONS_LAND.LOCATION_O,DPSTR_GIS.CONS_LAND.TYPE_LAW,DPSTR_GIS.CONS_LAND.TYPE_RIGHT -w "DPSTR_GIS.CONS_SHP_LAND.NSI_ID = DPSTR_GIS.CONS_LAND.NSI_ID" -i 5151 -s arcinfo.sibsac -u dpstr_gis -p dpstr_g

The below generates the following error:

ArcSDE 9.2 for Oracle10g Build 775 Fri Sep 17 10:45:27 2010
Attribute Administration Utility
-----------------------------------------------------
Error: DBMS table not found (-37).
Error: Unable to create view DPSTR_GIS.CONS_SHP_LAND_V

But if I convert the script to the query it is executed without error. Example:

Select
DPSTR_GIS.CONS_SHP_LAND.OBJECTID,DPSTR_GIS.CONS_SHP_LAND.SHAPE,DPSTR_GIS.CONS_SHP_LAND.NSI_ID,DPSTR_GIS.CONS_LAND.CAD_NUM,DPSTR_GIS.CONS_LAND.ALLOW_USE,DPSTR_GIS.CONS_LAND.LAND_CAT,DPSTR_GIS.CONS_LAND.DEN_AREA,DPSTR_GIS.CONS_LAND.LOCATION_O,DPSTR_GIS.CONS_LAND.TYPE_LAW,DPSTR_GIS.CONS_LAND.TYPE_RIGHT
from DPSTR_GIS.CONS_SHP_LAND,DPSTR_GIS.CONS_LAND
where DPSTR_GIS.CONS_SHP_LAND.NSI_ID = DPSTR_GIS.CONS_LAND.NSI_ID;

Re: DBMS table not found (-37)

VinceAngelo — Tue, 04 Sep 2012 10:00:13 GMT

Try removing the owner from the columns, and especially from the view name
(ArcSDE won't let you create a table as other than yourself) --

sdetable -o create_view ^
-T CONS_SHP_LAND_V -t CONS_SHP_LAND,CONS_LAND ^
-c CONS_SHP_LAND.OBJECTID,CONS_SHP_LAND.SHAPE,^
CONS_SHP_LAND.NSI_ID,CONS_LAND.CAD_NUM,^
CONS_LAND.ALLOW_USE,CONS_LAND.LAND_CAT,^
CONS_LAND.DEN_AREA,CONS_LAND.LOCATION_O,^
CONS_LAND.TYPE_LAW,CONS_LAND.TYPE_RIGHT ^
-w "CONS_SHP_LAND.NSI_ID = CONS_LAND.NSI_ID" ^
-i 5151 -s arcinfo.sibsac -u dpstr_gis -p ******

Re: DBMS table not found (-37)

AntonGonak — Wed, 05 Sep 2012 07:32:00 GMT

Hi, I tried to do as you suggest but nothing has changed. Generates the same error. Maybe the problem is in the user privileges?

Re: DBMS table not found (-37)

AntonGonak — Wed, 05 Sep 2012 08:53:37 GMT

How can I determine user privileges to create a view?

Re: DBMS table not found (-37)

VinceAngelo — Wed, 05 Sep 2012 09:45:31 GMT

You only need CREATE VIEW to create a view, but if it were a privileges issue you'd
get a different error than SE_TABLE_NOEXIST. What does 'sdetable -o describe'
return on the two tables?

I'd direct you to Tech Support, but 9.2 is no longer supported. You should make sure
you're using the terminal service pack (SP6) with all patches.

- V

Re: DBMS table not found (-37)

SachinKanaujia — Wed, 05 Sep 2012 18:08:35 GMT

I would suggest you to try testing it out on SDE user first to eliminate access related issues.

Re: DBMS table not found (-37)

VinceAngelo — Wed, 05 Sep 2012 18:39:22 GMT

'SDE' should have fewer privileges than a data creator (in general, and with respect to
the 'dpstr_gis' user's tables), so this would expose the instance to potential corruption
without providing any benefit.

- V

Re: DBMS table not found (-37)

SachinKanaujia — Fri, 07 Sep 2012 16:37:32 GMT

The suggestion ofcourse is to create everything within SDE (tables and views) for testing and eliminating the possibility of a bug. At times there are patch issues or could be an issue with schema permissions. If SDE can create it then it could be a permission issue where the user does not have required permission.

ESRI should seriously work on their error and exception handling though 🙂 !!!!

'SDE' should have fewer privileges than a data creator (in general, and with respect to
the 'dpstr_gis' user's tables), so this would expose the instance to potential corruption
without providing any benefit.

- V

Re: DBMS table not found (-37)

JuhoVainio — Mon, 16 Feb 2015 11:36:02 GMT

I'm having this issue also. It's so much fun trying to guess what is wrong when the error message doesn't help you at all. The most annoying thing is that it would take five seconds from ESRI to simply add the failing table name to the message and it would save me hours of tedious work.

Re: DBMS table not found (-37)

VinceAngelo — Mon, 16 Feb 2015 17:24:46 GMT

That "five seconds" estimate supposes that the database has provided this information in the integer error code made available to developers. I'd put the coding time to make 'sdetable -o create_view' easy to use on the order of 8-10 months (especially considering the potential differences of a dozen different RDBMS providers).

There is a better way to create views: Use SQL in the RDBMS directly. Once you've got the view working as desired you can then register it with ArcSDE via 'sdelayer -o register'. This is best practice, and I strongly recommend it.

- V

Re: DBMS table not found (-37)

JuhoVainio — Tue, 17 Feb 2015 08:07:19 GMT

sdelayer -o register doesn't work in this case because the view doesn't have a spatial column. From the docs: "Registers with ArcSDE a table having a spatial column".

Re: DBMS table not found (-37)

VinceAngelo — Tue, 17 Feb 2015 14:48:09 GMT

If the view doesn't have a spatial column then there isn't any need to use any ArcSDE command-line utilities on it for any reason. 'sdetable -o create_view' exists exclusively for the creation of spatial views against layers with SDEBINARY/SDELOB geometry storage. All other views should be constructed using database tools (or database tool wrappers, like Create Database View (Data Management)‌). Spatial views constructed with SQL against ST_GEOMETRY or native geometry columns must be registered with ArcSDE before they can be used from ArcGIS for Desktop as simple feature classes (as opposed to Query Layers, which do not require registration). Views cannot be registered with the geodatabases as full feature classes because that requires an SDE-set registered rowid column, and views can only have USER-set rowids.

- V

Re: DBMS table not found (-37)

JuhoVainio — Wed, 18 Feb 2015 08:08:15 GMT

If I don't register the view using the sdetable command, then I cannot publish the view in the ArcGIS server and query the data using the ObjectID column. Now that I've "tricked" the SDE into believing that my view is an SDE table, I can successfully query the data from the view using ArcGIS server.