topic Re: Table not found error while trying to register SQL spatial table in Data Management Questions

Table not found error while trying to register SQL spatial table

GangYang — Tue, 15 Nov 2011 07:36:11 GMT

Hello!

I'm trying to register a SQL Server spatial table in ArcSDE with following command:

sdelayer -o register -l dbo.baabLeitung_l,gFeature -C OID,SDE -e l3+ -R 6 -i sde:sqlserver:db-server2008\sql2008 -u sa -p mypwd -s DB-Server2008 -t GEOMETRY -D doeverden

As you see, I use a direct connection to the database server. The spatial table has a geometry column of gFeature. It has line geometry (linestring). If I execute the command, the following error will be raised every time:

DBMS table not found (-37)

But the table is there and can be listed with the command:

sdetable -o describe -t dbo.baabLeitung_l -i sde:sqlserver:db-server2008\sql2008 -s DB-Server2008 -D doeverden -u sa -p mypwd

I used here the same connection and the listed table can be checked in the attachment.
I could not figure out what the error means. Perhaps someone can help!

Thank you.
Gang

Re: Table not found error while trying to register SQL spatial table

VinceAngelo — Tue, 15 Nov 2011 10:05:28 GMT

It's a *really* bad idea to do any table creation in a database as the administrative user
(sa, SYSTEM, root,...). The same goes for using 'sde' or any other internal management
account. Instead, create ownership accounts and roles and manage access to the created
objects through roles.

In this particular case, the problem is that the login does not match the user and schema,
since 'sa' maps to 'dbo'.

- V

Re: Table not found error while trying to register SQL spatial table

GangYang — Tue, 15 Nov 2011 12:36:18 GMT

Thank you for your reply, Vangelo!
I'm just testing the work flow: sql spatial -> register -> edit in ArcGIS. The owner of the table is sa. And one of the condition for the registration (according to the online help): only the owner of table is allowed to do this, so I used the 'sa'

You said the problem is that the login does not match the user and schema. But if 'sa' maps to 'dbo', it should be ok!? What would be the correct syntax?

Re: Table not found error while trying to register SQL spatial table

VinceAngelo — Tue, 15 Nov 2011 13:05:28 GMT

*User* 'sa' does not own the table, *login* 'sa' does (as user 'dbo'). This is not one of the
most compelling reasons not to use 'sa' for table creation, but it is a critical problem in your
workflow.

The documentation also recommends creation of data ownership accounts, and provides the
guidance that login must match user must match schema. Registration will function if you
follow this guidance.

- V

Re: Table not found error while trying to register SQL spatial table

GangYang — Tue, 15 Nov 2011 14:20:09 GMT

Thank you again.

If I understand it correctly, the option -u of sdelayer requires a login (for my case sa). SQL Server maps it to a database user dbo. I checked that in SQL Server. All seems to be correct (->attachment UserMapping.jpg)?!

Perhaps could you give me an example, how the login/user/schema/ should look like?

Re: Table not found error while trying to register SQL spatial table

VinceAngelo — Tue, 15 Nov 2011 16:46:05 GMT

Re: Table not found error while trying to register SQL spatial table

GangYang — Wed, 16 Nov 2011 07:49:15 GMT

Hi, I followed your suggestion and created a login 'doeverden', a user 'doeverden' in the database and this user has a schema with the same name (please refer to the attached picture). The user is also the db-owner. With this login i can list the table (with sdotable -o describe), the connection in arccatalog has also no problem to show the table.

Unfortunately the registration fails as before with the error "DBMS table not found (-37)". What could be wrong? Is there any more detailed documentation on this registration issue?

Thank your for your patience.

Re: Table not found error while trying to register SQL spatial table

VinceAngelo — Wed, 16 Nov 2011 10:27:46 GMT

If the user is the database owner, then you have login 'doeverden', user 'dbo'.

While I was first enamored by the Sybase characteristic that a login could map to different users,
I soon began to loathe what it meant for applications trying to identify which login was associated
with what user. I've avoided using 'dbo' since the mid-90's, don't know how (or if it's even possible)
to make it function with ArcSDE. Please contact Tech Support for further assistance in this matter.

- V