Problem: Problems displaying and/or querying MapServices that use ArcSDE layers


I am having problems displaying and/or querying my MapServices that use ArcSDE layers. What is causing this problem?


The problem occurs with MapServices that use ArcSDE layers that do not have their ROWID calculated. Starting at version 8.0.x of ArcSDE, a ROWID column must be present.

The ROWID column is a primary key that is used by ArcGIS and ArcIMS to access the features in the database. A ROWID column is created and registered to shapefiles and coverages converted through ArcCatalog. However, layers created by shp2sde and cov2sde and layers from SDE 3.x loaded through sdeimport into ArcSDE 8.0.x and 8.x will not have the ROWID column created.

If no ROWID column is present in the ArcSDE 8.x layers:

· MapServices that have ArcSDE layers without ROWID columns will display and can be queried except for point layers in ArcSDE 8.1/Oracle and ArcSDE 8.1/SQL Server.

· Feature MapServices with point layers in ArcSDE 8.1/Oracle and ArcSDE 8.1/SQL Server will not display, identifies will not work, and point features cannot be selected.

· Image MapServices with point layers in ArcSDE 8.1/Oracle and ArcSDE 8.1/SQL Server will display, but point features cannot be identified and cannot be selected.

Solution or Workaround

The ROWID column can be created with the following command:

sdelayer -o register -l sdelayer,shapecolumn -e a -c objectid -C SDE -u databaseusername -p databaseuserpassword

The syntax is:

sdelayer -o register -l <table,column> -e <entity_mask>
[{-c <row_id_column> -C {SDE | USER} } | {-C NONE}]
[-k <config_keyword>] [-S <layer_description_str>]
[-i <service>] [-s <server_name]
-u <DB_User_name> [-p <DB_User_password>] [-q]

ArcCatalog names the ROWID column OBJECTID. To determine whether a column named OBJECTID is present, you can check the fields listed in the Query Builder of ArcExplorer 3.1—Java Edition or, in ArcCatalog, use table preview to display the fields.

You can also use the following SQL command:

select rowid_column,table_name from sde.table_registry where rowid_column is not null;

ESRI recommends that a ROW ID column be added to all ArcSDE 8.0.x and ArcSDE 8.1 layers. Layers with a ROWID column, regardless of the type of features in the layer, will always support display, query, and selection. Creation of a ROWID column does not mean that a new column must be added to the database. Any column in a table that is unique, non-null, and an integer can be used as the ROWID column.

Using ArcSDE command-line utilities, such as sdetable or sdelayer, to register tables or layers with the SDE.TABLE_REGISTRY will NOT register them with the Geodatabase (the SDE.GDB_% set of tables).