Ready to take a baseball bat to SDE Server

[ShonHarris]

I will be the first to admit I am a newbie when it comes to managing an SDE Service. But I have never had so many problems with one peice of seemingly small software.

So here is the deal:

I built an SDE (DBO Schema) Database, and my SQL Reporting Guru has been working with it.  In the intrest of security, and isolation for a sandbox I built a new instance of SQL Server, restored the Database from a backup, with the schema and views my reporting analyst created and then resynced the logins. I can login with my sde user from remote ODBC, and SQL Management Studio, as well as Local ODBC and SQLSMS on the SQL Box.

When I try to create the new SDE, it attempts to login, but I get an error.

When I start the service the GIOmgr_esri_sde shows this:

Thu Mar 07 14:35:19 2013 - ****** Spatial Database I/O Manager ****** Thu Mar 07 14:35:19 2013 - SDE System Startup Initiated . . . Thu Mar 07 14:35:19 2013 - Validated System Paths. Thu Mar 07 14:35:19 2013 - SDE IOMGR going into background . . . 

And when I look in the sde_esri_sde log I see this

Connected to instance . . . ERROR in checking system stored procedures (1). Run the Upgrade Geodatabase tool to install/upgrade system tables. Refer ArcSDE installation guide for instructions. 

I have looked high and low, and spent way to long on this error - so I am hoping someone has some insight as to what i need to do to get this back online.

Thanks in advance.

[AsrujitSengupta · Accepted solution]

The error message clearly states that the sde geodatabase needs to be upgraded before the service can be started.

Create a Direct Connection to that sde geodatabase using a "sysadmin" user. Right click on the connection-->Properties-->General-->Upgrade the geodatabase to the current version..

Make sure the ArcGIS Desktop is at the same level as ArcSDE to avoid any issues (even Service packs matter). The issue might be encountered is you are installing new Service Packs for ArcGIS Desktop and ArcSDE but not upgrading the Geodatabase. In these scenarios the geodatabase stays at the old level, whereas the ArcSDE has been upgraded.

A simple search on Google with "ERROR in checking system stored procedures (1).
Run the Upgrade Geodatabase tool to install/upgrade system tables.", led me to this page:
http://forums.arcgis.com/threads/32176-Upgrading-ArcSDE-10-installation-to-sp2

I hope this helps in resolving your issue.

Best Regards,

[ShonHarris]

I found that same article within minutes after posting it.

By making the direct connection, I was able to get into the GeoDatabase, and see everything however, when I tried to upgrade, the "Upgrade Geodatabase" could not be selected (it was greyed out)

Does this mean I need to run ARCMap 10.0 SP 5 on the workstation?

[AsrujitSengupta]

If the ArcSDE is at 10.0 SP5, then yes. You need to install the ArcGIS for Desktop 10.0 SP5 on the client.
http://support.esri.com/en/downloads/patches-servicepacks/view/productid/66/metaid/1876

Make sure you are connected as a "sysadmin" or "db_owner" user. That's a prerequisite for an upgrade.

Regards,

[ShonHarris]

If the ArcSDE is at 10.0 SP5, then yes. You need to install the ArcGIS for Desktop 10.0 SP5 on the client.
http://support.esri.com/en/downloads/patches-servicepacks/view/productid/66/metaid/1876

Make sure you are connected as a "sysadmin" or "db_owner" user. That's a prerequisite for an upgrade.

Regards,

Is there a script i can use to verify that my SDE user has the correct permissions on the Geodatabase?

[AsrujitSengupta]

Shon,

I don't really have any at my disposal right now, but there are many such scripts available on the net.

Check if this helps:
http://www.pythian.com/blog/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a...

You can quickly verify from SQL Server Management Studio though right now.

[ForrestJones]

To check if the sde admin user has correct privileges to upgrade you can run the Upgrade geodatabase tool Pre-Requisite Check (use GP tool or right-click connection > properties > general tab > upgrade status > click upgrade geodatabase button):

For enterprise geodatabases, it determines whether
The connected user has the appropriate privileges to upgrade.
The connected user is not editing data in the geodatabase.
There are no other users connected to the geodatabase.
The database is enabled to support XML data types.
All the information within the current geodatabase system tables can be opened.
For geodatabases in PostgreSQL and Oracle, it detects whether the ST_Geometry library in the database location is current.
For geodatabases in a user's schema in Oracle, it detects whether the master geodatabase is upgraded.

From: Upgrade Geodatabase (Data Management)

[ShonHarris]

So I got the SDE Connected, and we got the database upgraded.
Everything was running swimmingly.

Then I walked away for lunch, and I can't get the SDE Connected again. I can connect to the SQL Server through ODBC and SQLMS on that same machine, using Windows and SQL Server authentication, but do you think the SDE would connect? Nope. Not a chance.

[AsrujitSengupta]

Shon,

I think you should consider contacting the ESRI Tech Support. Maybe if the Support Analysts have a look at your environment and settings, they can help you in a better way.

Regards,

[ShonHarris]

We got it fixed, it was a combination of firewall issues at the endpoint we were trying to have connect from a different department and SELECT grants on the user trying to login.

Thanks so much for your insight.