Bug: Unable to Preview Feature Classes or Upgrade a Geodatabase from the ArcGIS 10 Client

Description

The following are the two situations in which this problem is encountered:

Working with data

When making a Direct Connect from an ArcGIS 10 client to a version 9.2, 9.3 or 9.3.1 geodatabase on SQL Server, interacting with the data fails, such as previewing in ArcCatalog, fetching layer properties, adding layers to ArcMap, etc.

For example, previewing a feature class in ArcCatalog returns the following error:

"DBMS table not found [GDB_water.GIS.Water_Manhole][STATE_ID=0]"

Upgrading a geodatabase to ArcGIS version 10

Upgrading a version 9.2, 9.3 or 9.3.1 geodatabase on SQL Server to version 10 returns the following types of errors and warnings on all datasets:

"Adding feature class (GDB_water.GIS.WaterMain_ABN).
Table (GDB_water.GIS.WaterMain_ABN) was found in GDB_ObjectClasses, but does not exist in the database. Skipping.
DBMS table not found [GDB_water.GIS.WaterMain_ABN][STATE_ID = 0]
Adding feature class (GDB_water.GIS.Valve).
Table (GDB_water.GIS.Valve) was found in GDB_ObjectClasses, but does not exist in the database. Skipping.
DBMS table not found [GDB_water.GIS.Valve][STATE_ID = 0]"
�
"Adding geometric networks.
Could not open geometric network.
DBMS table not found [GDB_water.GIS.N_4_Props][STATE_ID = 0]"
�
"Failed to execute (UpgradeGDB).
Failed at Fri Jul 23 09:37:20 2010 (Elapsed Time: 33.00 seconds)"

Cause

This is a known issue with ArcGIS 10 clients making direct connections to some version 9.2, 9.3 or 9.3.1 geodatabases on SQL Server. This problem is caused by a query describing the columns on a table that incorrectly filters by user_name instead of schema_name. In databases where the schema_id of a schema and the principal_id of a user do not share the same number, the query returns no data.

To verify that this problem is present in a geodatabase, execute the following query in SQL Server as the DBO user or a member of the db_owner role:

Code:
select s.name as schema_name, s.schema_id, dp.name as user_name, dp.principal_id  
from sys.schemas s join sys.database_principals dp 
on s.name = dp.name 
and s.schema_id <> dp.principal_id

Example returns:

schema_name	schema_id	user_name	principal_id
map		10		map		6
gdb	        11		gdb		7
catalog		12		catalog		8

In this example, data from the map, gdb or catalog schema are inaccessible.

Workaround

• For ArcGIS 10 clients working with data in version 9.2, 9.3 or 9.3.1 geodatabases, use an Application Server connection instead of Direct Connect.

• To upgrade a geodatabase in this state, apply the ArcSDE 10.0 General Update Patch for SQL Server.

ArcSDE 10.0 General Update Patch for SQL Server

Article ID:000011001

Software: