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)"
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:
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
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.
• 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