SDE v10 SP1 upgrade geodatabase succeeds but SDE_Version still shows 10 0 0

[JonHall]

Running ArcSDE 10.0 and ArcGIS Desktop 10.0 on WindowsXP pro SP3, SQL Server 2008 R2
Logged into Windows as an administrator
Installed Service Pack 1 for ArcGIS Desktop 10.0 (ArcGISDesktop10sp1.msp)
Stopped the SDE services for 8 spatial databases
Installed Service Pack 1 for ArcSDE Desktop 10.0 (SDE10sp1-SqlServer.msp)
Restarted the machine
Re-Stopped the SDE services for 8 spatial databases
Opened ArcCatalog 10.0,
   Direct Connect as OSA to first spatial database
   Ran ArcToolbox > data management > database > Upgrade Geodatabase
   Results window indicates success

[INDENT]Executing: UpgradeGDB "Database Connections\DirectConnect to WSPC328 BooneIL as OSA.sde" PREREQUISITE_CHECK UPGRADE
Start Time: Thu Jan 27 14:40:14 2011
Paused the database for upgrade.
There are no other active connections.
User has privileges required to upgrade.
Running Pre-Requisite check (C:\Documents and Settings\jhall\Application Data\ESRI\Desktop10.0\ArcCatalog\DirectConnect to WSPC328 BooneIL as OSA.sde).
Instance supports XML type.
ArcSDE server tables and stored procedures are up to date.
Unpaused the database.
Succeeded at Thu Jan 27 14:40:23 2011 (Elapsed Time: 9.00 seconds)[/INDENT]

Re-started the SDE service for the upgraded spatial database - no problems connecting after upgrade to SP1.

Open SDE.SDE_Versions table in SQL Server Management Studio to double-check Major, Minor, Bugfix, and Release.

SDE v10 SP1 upgrade geodatabase succeeds,
PatchFinder.exe reports both service packs installed
[INDENT]ArcGIS Desktop 10
ArcGIS Desktop 10 Service Pack 1

ArcSDE for Microsoft SQL Server 10
ArcSDE for Microsoft SQL Server 10 Service Pack 1[/INDENT]

but SDE_Version table still shows same values as before applying Service Pack 1:

MAJOR = 10
MINOR = 0
BUGFIX = 0
Description = for SQL Server Build 685 Fri May 14 12:05:43  2010
RELEASE = 100001
SDESVR_REL_LOW = 93001

"Upgrade Geodatabase" command button is disabled, in Properties of the ArcCatalog database connection, after upgrade.

So this SP1 repository update must be succeeding, but it's not recording the update in SDE_Version table.

Any one know what's going on, and where ArcSDE 10 is now storing this release version info?

[EugeneYang]

The ArcSDE release number in the VERSION table does not always get updated after applying a service pack. It is not an indicator of whether your geodatabase schema was upgraded correctly after applying a service pack.

To make sure that you have apply a service pack correctly, you can verify geodatabase upgrade by following steps,

1) Run Patch Finder against your ArcGIS Desktop installation, and verify that the service pack has been applied.
2) In ArcCatalog, make a direct connect to the geodatabase, and go to the Upgrade Status section on the Database Property dialog. If the Upgrade Geodatabase button is disabled, the service pack has been applied correct in your geodatabase schema.

Eugene

[JonHall]

Eugene-
RE: The ArcSDE release number in the VERSION table does not always get updated after applying a service pack

through ArcSDE 9.3.1 SP2, I was under the impression that the ArcSDE release number in the VERSION table always got updated after applying a service pack

Jon D of ESRI posted this BLOG:
http://blogs.esri.com/support/blogs/supportcenter/archive/2010/03/05/do-you-know-what-version-of-arc...

[EugeneYang]

The blog post should have been clearer on this.

When it says this about SDE verion table,

"Within your ArcSDE respository is a table named �??VERSION�?� that contains the major and minor release and the build of the ArcSDE repository in which you are working. An ESRI Support Analyst can work with you to determine which release of the software you are using with the information stated above",

the "release of the software" means major release.

As you can see when it refers to the Patch Finder, it specifically says "releases, service packs, and patches you have installed".

Sorry for the confusion. The method mentioned in my previous reply is a better way verify service pack application.

[JonHall]

Eugene-
As an ESRI software user for 15 years, a developer, an ArcSDE DBA, and an ESRI business partner, I need to know how it works "under the hood". 

The PatchFinder queries the Windows registry for software version installed - I get that.

WHAT table (or other geodatabase object) is the Upgrade Geodatabase button querying to determine it's enabled state?

It's definitely querying something stored inside the database that identifies the schema version - I know that much.

Do I have to open an ESRI tech support to get this answered?
Thanks

[EugeneYang]

Let me start by saying that I�??d like to understand the context for your question.  What is the reason that you need to query if the geodatabase upgrade has been successful?  Is it because another process is dependent on the successful completion of the upgrade?  Is it because you�??ve done this in the past and performing this query is another confirmation the upgrade was successful?  Or, is it a different reason.

Whether the Upgrade Geodatabase command and tool are enabled is based several queries, one of which is �??Does the Geodatabase need to be upgraded?�?�.   This query examines the internals of the geodatabase (the collection of tables used for persisting information and a collection of database packages, procedures, functions for executing geodatabase behavior) to determine if it needs to be upgraded.  If either of these two collections (the tables or the database packages, et al) need to be upgraded, then the Upgrade Geodatabase command and tool are enabled.

This is a change in behavior at 10.0.  At previous releases we stated that the sdesetup �??o  upgrade command should always be run, whether it needed to or not, after a QFE (Service Pack, Patch or Hot Fix) were applied.  We�??ve changed this behavior to make it clear when you have to run the upgrade command and to try and simplify the upgrade process for all Geodatabases.

Determining if the collection of tables makes up the Geodatabase needs to be upgraded is straightforward.  However, determining if the collection of database packages, et al need to be upgraded is not as simple, as there are multiple queries that need to be run to check whether an upgrade is necessary based on the software component updates and the values of the version table, stored procedures and/or geometry type implementation.  

Determining what release of the ArcSDE technology that you have has never been a simple process.  Unfortunately, that does not change at 10.0.  As I said at the beginning, understanding what it is you�??re trying to do and why will help me in answering this question.

[JonHall]

...and a collection of database packages, procedures, functions for executing geodatabase behavior...

Eugene-
Thank you for the clarification!
reason: "I�??ve done this in the past and performing this query is another confirmation the upgrade was successful."

I support literally dozens of ArcSDE databases, and 2 or 3 times a year, something unusual happens.  I'm usually able to resolve the customer's ArcSDE problems, without escalating their issues to ESRI for support.

Occasionally I've had to verify the version of a customer's geodatabase, using a remote connection to their database server. In the past, occasionally a customer has run the service pack MSI, but not run SDESETUP -o Upgrade; now they've got to remember to run ArcCatalog's Upgrade Geodatabase utility.

Typically, database servers do not have ArcGIS installed, only SQL Server Management Studio. Uploading PatchFinder.exe to verify the ArcSDE version is usually straightforward, requires no license, and it's a tiny EXE file; uploading and installing a copy of ArcGIS Desktop onto a remote server is another matter, even if there is a license available.  Getting a local copy of ArcCatalog to connect to an ArcSDE database on a remote server is not always possible.

Querying the SDE_Version table, to check MAJOR, MINOR, BUGFIX (or examining the XML definition of the workspace record in GDB_Items) is straight-forward.

If the enabled property of the Upgrade Geodatabase button were using the IGeodatabaseRelease.CurrentRelease property, I could write a piece of ArcObjects code to check this.  But I would have to run the code from an ArcGIS Desktop client, and if I could do that, then I could just check the Upgrade Geodatabase button in ArcCatalog.

I would like to be able to independently verify the service pack level, using SSMS to "examine the internals of the geodatabase", tables, stored procedures, functions, etc. Is that not possible?

Thanks,
Jon

[EugeneYang]

Jon,

�??I would like to be able to independently verify the service pack level, using SSMS to "examine the internals of the geodatabase", tables, stored procedures, functions, etc. Is that not possible?�?�

Unfortunately, the answer is that it depends on the type of update that�??s being installed.

To verify a major release upgrade, such as upgrade to 9.3.1 or 10, you can query the SDE_VERSION table and the GDB_ITEMS table (GDB_RELEASE before 10). These release numbers keep track of geodatabase schema table updates and reflect major release change.

However, not all QFEs (which include Service Packs, Hot Fixes and Patches) involve schema table changes.  The majority of changes made in a QFE that require the upgrade to be run are fixes to stored procedures (and ST geometry type on some DBMS).  These stored procedures have their own release numbers.  While you can query the stored procedures through SQL to determine its release, interpreting the release numbers is not straightforward.  This has always been in issue, it�??s not something new at 10.0.

We are working on a solution to this in 10.1, a way to query if the geodatabase is at the current release or not and have it reflect all aspects of the geodatabase state (whether there is a major upgrade or a QFE).

Until then, the two options listed below are the only supported methods to verify geodatabase upgrade which guarantee that you will get the correct answer:

-          Check disable/enable of the upgrade button on geodatabae property dialog
-          Use IGeodatabaseRelease.CurrentRelease property in ArcObjects code

Thanks
Eugene

[JonHall]

Eugene-
Thank you for taking the time to provide the detailed explanation! I greatly appreciate it.
-Jon