Unable to get permissions info on table

[StephenLam]

I am using sdeexport command to export my feature class from MS SQL Server 2012. I am sure that the table is existing and connection is correct.

C:\Windows\system32>sdeexport -o create -t my_db.dbo.my_feature -f my_feature -s DB1 -D DEV -u sa -p "password"

ArcSDE 10.2

SDEX File Export         Administration Utility

-----------------------------------------------------

Exporting ArcSDE object to "my_feature" in SDEX export format ...

SDE Code (-162)= Invalid database name

Unable to get permissions info on table "my_db.dbo.my_feature"

    0 features converted.

    0 features exported.

And also, I found the following log in sde_esri_sde,log

C:\Users\ADMINI~1\AppData\Local\Temp\2\arcE506: No such file or directory

[Mon Sep 22 17:39:32 2014] [0] [AFCD-BDS-GD1] WARNING: SDETMP: Setting temp path to c:\temp

I was wondering if someone could help me with this problem, thanks!

[VinceAngelo]

Why do you specify "-D DEV" when the the table appears to be in "my_db"?

BTW: It's NOT recommended to do ANYTHING in an ArcSDE geodatabase as 'sa'.

- V

[StephenLam]

Hi VInce, thanks for your reply, I have removed the flags "D" and "s", although the error is still occurred, the error log messages have been changed as below.

Command:

C:\Windows\system32>sdeexport -o create -t my_db.dbo.my_feature -f my_feature -u sa -p "password"

ArcSDE 10.2

SDEX File Export         Administration Utility

-----------------------------------------------------

Exporting ArcSDE object to "my_feature" in SDEX export format ...

SDE Code (-162)= Invalid database name

Unable to get permissions info on table "my_db.dbo.my_feature"

    0 features converted.

    0 features exported.

Log:

Wed Oct 08 16:59:08 2014 - SDE Server Pid 7160 Registered, User: DBO, Schema: sde, Machine: MAP1.

Wed Oct 08 16:59:08 2014 - SDE Server 7160 exit'd with status 0

Wed Oct 08 16:59:08 2014 - SDE Server Pid 7160 Stopped, User: DBO, Schema: sde, Machine: MAP1.

[VinceAngelo]

The "-D" is required for a Direct Connect.  The database name prefix should not be on the table ('-t') parameter.

It's still not recommended to use 'sa' with command-line tools.

You should probably connect as the table owner with an 'sdelayer -o describe' to be certain of what ArcGIS considers the table name.

- V

[StephenLam]

Thx for your advice, I will create another account for sde export for long-term usage.

After I use the "sdelayer -o describe -u sa -p password" command, I found that the default database is referring to my sde admin db.

ArcSDE 10.2

Layer    Administration Utility

-----------------------------------------------------

----------------------------------------------------------------

Database           : SDE

Table Owner        : SDE

Table Name         : TEST

Spatial Column     : SHAPE

Layer id           : 3

Entities           : npc

Layer Type         : In-Line Spatial Type

I/O Mode           : NORMAL

Autolocking        : Enabled

Precision          : High

User Privileges    : SELECT, UPDATE, INSERT, DELETE

Layer Configuration: DEFAULTS

However, I want the sdeexport can export the table from the database "my_db". The server information is shown as below, would you please advice the properly command and setting to export the feature? Thanks.

Server\Instance: DB1\DEV

Database: my_db

Owner: sa

Table name: dbo.my_feature

[VinceAngelo]

How did you create this instance?  I abandoned multi-database storage at ArcSDE 9.0, and have never attempted to create a table as 'sa', so I've never seen a deployment like this (and didn't think it was technically feasible to do so since 9.3).

What is the exact contents of the SDE.SDE.LAYERS table?  Is there a row that corresponds to your target layer?  What database is recorded for the table?

- V

[StephenLam]

My another project is using oracle 11g and ArcGIS 10.1, it is working properly with multi-database. And this is my first time to use SQL server for ArcGIS Server.

While I query the "[sde].[SDE_layers]", I found that there are 2 features (table_name=GDB_ITEM, TEST), and obviously I cannot find any feature class information that is stored in this table. However, "[MY_DB].[dbo].[SDE_layers]" has contained the features that I want to export.

Let me introduce my ArcGIS server setup background. Esri team helped me install ArcGIS server and ArcSDE service, and then a schema "SDE" was created in my SQL server. My client wanted to separate the the business data and create another database schema "my_db", so all of my features are stored in "my_db". Also, I used ArcCatalog to register "my_db" and create a database connection to it.

As such, what steps should I do to let the sdeexport stop reading "sde", but "my_db" instead? Thank you.

[VinceAngelo]

Oracle does not support multiple databases the way that SQL-Server does (until Oracle12c, and ArcGIS doesn't yet support multi-tenant databases), so you're not using the multi-database model to which I was referring (which I believe to be deprecated).

Given the metadata you provided, if sdeexport is going to work, it would be with:

     sdeexport -o create -D my_db -t dbo.my_feature -f my_feature -u sa -p "password"

or possibly just

     sdeexport -o create -t dbo.my_feature -f my_feature -u sa -p "password"

It's actually against best practice to create a database named "SDE" in SQL-Server, and to place data which will be used together in multiple databases (it significantly impairs performance to join across databases).  I don't ever use SDO-owned tables, mostly because of the peculiar security model it produces.

Best practice is to create logins, users and schemas for each logical owner, and to use Direct Connect with ArcGIS Desktop to manipulate the tables (if geodatbase functionality exists in the feature class, the output of 'sdeexport' may be corrupt, or at least not useful for recreating the previous contents).  Use of application servers (and the sdeexport command) is deprecated at ArcGIS 10.2, and will not be available at 10.3.

- V

[StephenLam · Accepted solution]

Finally, I get it works...I have 2 esri_sde services...one is "esri_sde", another is "n_esri_sde". I don't know why I have already specify the "-i n_esri_sde" but it keeps using esri_sde.

BTW, Does there any other command tool for me to export and import the feature? Would you please suggest the best way to do so? If I just have to export the table structure of a feature, does the command also support? Thank you.

[VinceAngelo]

Yes, you must specify the "-i" instance if it is not the default ("esri_sde") or the contents of the SDEINSTANCE environment variable.

ArcGIS can export feature classes schema-only in XML.

- V