For the past two weeks I have been dividing my time between job interviews and moving a GeoDatabase from a Data Center in Spain to Israel. The GeoDatabase in question was SQL Server 2008 with SDE 10 and it was moved to a development server that at first glance seemed to have the same installation.
The moving of the Database was done by using SQL Server backup and restore of both the SDE database and our data database. The first problem that popped up was of Orphaned Users (I wasn’t the one to fix it but found this solution).
The next problem was over zealous IT people that gave everyone SA (System Admin) rights on the database, why is that a problem? well lets say user UserX has permissions to query a table named UserX.Points in the database and he has a default schema of UserX all he has to do in a query is:
SELECT * FROM Points
(This is also the default way we queried the DB from our application)
But if the user is defined as a SA then he must query with the full name of the table:
SELECT * FROM UserX.Points
The default schema name seems to be ignored.
The next problem was this error in ArcGis Server:
The Layer:'DB_NAME.SCHEMA_NAME.LAYER_NAME' in Map:'Layers' is invalid. This release of the GeoDatabase is either invalid or out of date. DBMS table not found [Microsoft SQL Server Native Client 10.0: Invalid object name 'sde.sde.GDB_Release'.] [sde.sde.GDB_Release]
The first thing I tried was opening SQL Server Management with the sde user and trying to query sde.sde.GDB_Release. The object can’t be found. Next I tried the SA user – failed as well, the table just didn’t exist. Checking the DB in the Data Center revealed that the missing table wasn’t there either – but there the ArcGis Server worked. I came into a conclusion that the table was from Sde 9.3.1 and in the upgrade to 10 (for some unknown reason) it is still kept (but not when doing a fresh install of version 10).
I have used the Sde command line tools to check what layers are in the DB: they were all there.
At that point I realized I needed to open ArcCatalog/ArcMap and try to solve the problem, but the PC where our license was stored was missing (all the PCs were turned off, stored in one storage room and there wasn’t any identifying marks on that specific PC). So I made a call to Systematics (the local retailers and experts on ESRI software) they gave me a link to a free trial of ArcGis Desktop 10.1 installing that didn’t solve the problem just made it weirder:
Using ArcCatalog I was able to connect to the features in the Database (as seen from the Sde command tools).
Using ArcMap I was able to open the ArcGis Server MXD and even save a document in the format of 10 but the error persisted.
I have made a backup of the geographic data on a File GeoDatabase and tried unistalling the Sde, deleting the Sde database, reinstalling the Sde (version 10) and then doing the restore again. In the thought that maybe using a fresh installation of Sde 10 will solve the problem (as opposed to an upgrade from 9.3.1). It didn’t work same error as before…
At that point I decided to broaden my options and install Sde on an existing DB server and ArcGis Server on another server. The new Sde was (this time) installed using our deploying application (instead of the backup/restore method) and then the data was imported using ArcCatalog. Trying to use a MXD with layers from this DB resulted with the error:
The Layer:'DB_NAME.SCHEMA_NAME.LAYER_NAME' in Map:'Layers' is invalid. The connection property set was missing a required property or the property value was unrecognized.
I tried both MXDs on the new ArcGis Server – the same errors (meaning the problem was not in the server).
I tried using data from a File GeoDatabase – that worked without any problems (in ArcGis Server) but trying to use it in our application caused the queries to return empty results because we were using where clauses that looked like Modified > ‘2000-01-01’ and it seems File/Personal GeoDatabse doesn’t support this type of date queries. I used it though as a temporary fix because some actions could be done even though no features were displayed in the map.
I tried creating a new Feature using ArcCatalog and trying to view just that feature – failed as well. The thought here was that maybe the deployment application caused the problem.
I have become a bit desperate so I even tried manually creating GDB_Release table in my original DB, found this reference that had all the fields of the table and tried entering versions:
- Major: 10 Minor: 0
- Major: 9 Minor: 3
- Major: 9 Minor: 4
For all of them I got the new error:
The Layer:'DB_NAME.SCHEMA_NAME.LAYER_NAME' in Map:'Layers' is invalid. This release of the GeoDatabase is either invalid or out of date.
Then the people from Systematics saved the day and freed our licenses (because it seems that you have to deauthorize a license before you use it in another PC). After installing ArcDesktop 10 I got this error trying to connect to the original Sde (just by testing the connection):
This is the same Sde server that worked with ArcDesktop 10.1…
But the new Sde worked just fine, and after creating a MXD file ArcGis Server worked as well.
At the end we decided to use the new Sde and mark the old one as an example of what happens when you upset ESRI’s software…
Conclusion: When transferring GeoDatabases don’t use Backup/Restore (and if you do then keep a backup of the old version of those databases) instead use ArcCatalog to transfer the feature data (you can even use it to transfer non geographic data). Unless of course you are feeling lucky! Are you feeling lucky, punk?
I would like to thank Mody Buchbinder and Yovav Zohar from Systematics, without them the problem would never have been solved.
