ArcSDE 9.2 SQL Server Case Sensitivity Patch

This patch will assist users in converting existing 8.3, 9.0 or 9.1 SQL Server geodatabases with a case-sensitive database collation to a 9.2 geodatabase using a case-insensitive collation. After applying the patch users will be able to enable case-sensitive collations on individual character data columns.

NIM009399

Beginning with ArcGIS® 9.2, SQL Server geodatabase identifiers, such as feature class or table names, cannot be case sensitive. This means the contents of the ArcSDE® and geodatabase repository tables (SDE_ and GDB_ tables) cannot be created and stored using a case-sensitive collation. Your database collation must be case insensitive, however individual character data columns can store case-sensitive data. This patch will assist users in converting existing 8.3, 9.0 or 9.1 SQL Server geodatabases with a case-sensitive database collation to a 9.2 geodatabase using a case-insensitive collation. After applying the patch users will be able to enable case-sensitive collations on individual character data columns.

Installing the Patch

ArcSDE 9.2 Service Pack 2 must be installed before you can install this Patch. It is strongly recommended that you back up your database including all previous ArcSDE system tables and user layer data before upgrading your ArcSDE installation.

REQUIRED INSTALL STEP: If you connect to ArcSDE 9.2 via an Application Server and Direct Connect connections using ArcGIS 9.2 or ArcIMS 9.2 make sure you also see ESRI Products connecting to ArcSDE (Application and Direct Connect connections).

Server Install for Windows

Windows Installation Notes:

ArcSDE 9.2 Service Pack 2 must be installed on the server and your client applications before you install this Patch.

Before proceeding, please ensure that the following is true:
- Your SQL Server instance must use a Case-Insensitive server collation. Geodatabase names cannot be case-sensitive. For information on setting or altering the SQL Server default server collation, see Setting and Changing the Server Collation in the SQL Server Books Online.
- You are running ArcSDE 8.3, 9.0 or 9.1. If you have installed ArcSDE 9.2 and have upgraded the repository tables through either the Post-Installation Wizard or the sdesetup -o upgrade command you must revert to the last backup of your geodatabase at the earlier version.

Installation & Upgrade Steps

Disconnect all users & stop the ArcSDE service.
Perform a full backup of all databases you are upgrading.
Uninstall ArcSDE.
Using SQL Server Query Analyzer or Management Studio, alter the SQL Server Database collation of all databases you are upgrading.
- 'sde' database or single spatial databases:
  - Drop the SDE_spatial_references constraints.
  - The SDE_spatial_references table contains two constraints that must be dropped before the database collation can be changed. The following Transact-SQL statements will drop the spatial_ref_xyunits and spatial_ref_zunits constraints. All code in this document references a database named sde. Modify the database name to match that of your geodatabase
    - USE [sde] GO ALTER TABLE [sde].[SDE_spatial_references] DROP CONSTRAINT [spatial_ref_xyunits]; ALTER TABLE [sde].[SDE_spatial_references] DROP CONSTRAINT [spatial_ref_zunits];
  - Alter the database collation.
    After the constraints are dropped, change the database collation using the ALTER DATABASE statement.
    - USE [master] GO ALTER DATABASE [sde] COLLATE SQL_Latin1_General_CP1_CI_AS;
    The collation used in the above statement is the default for most English-language databases. The best way to choose a collation is to see what your SQL Server instance collation is set to (available on the Server Properties dialog box), making sure that it is case insensitive. All case-insensitive collations use the letters CI in their name. Execute the statement:
    - SELECT * FROM fn_helpcollations
    to get a list of all the supported SQL and Windows® collations.
    
    For more information on collations in SQL Server, refer to the Understanding SQL Server Collations section.
  - Recreate the SDE_spatial_references table constraints.
  - After the database collation has been successfully altered, re-create the constraints on the SDE_spatial_references table.
    - USE [sde] GO ALTER TABLE [sde].[SDE_spatial_references] WITH CHECK ADD CONSTRAINT [spatial_ref_xyunits] CHECK (([xyunits]>=(1))) GO ALTER TABLE [sde].[SDE_spatial_references] CHECK CONSTRAINT [spatial_ref_xyunits] ALTER TABLE [sde].[SDE_spatial_references] WITH CHECK ADD CONSTRAINT [spatial_ref_zunits] CHECK (([zunits]>=(1))) GO ALTER TABLE [sde].[SDE_spatial_references] CHECK CONSTRAINT [spatial_ref_zunits]
- All non-'sde' databases from multiple spatial database instance:
  - Alter the database collation.
    After the constraints are dropped, change the database collation using the ALTER DATABASE statement.
  - - ALTER DATABASE [sde] COLLATE SQL_Latin1_General_CP1_CI_AS;
    The collation used in the above statement is the default for most English-language databases. The best way to choose a collation is to see what your SQL Server instance collation is set to (available on the Server Properties dialog box), making sure that it is case insensitive. All case-insensitive collations use the letters CI in their name. Execute the statement
    - SELECT * FROM fn_helpcollations
    to get a list of all the supported SQL and Windows ® collations.
    
    For more information on collations in SQL Server, refer to the Understanding SQL Server Collations section.
Install ArcSDE 9.2 (do not run sdesetup –o upgrade command or the Post-Installation Wizard)
Install Service Pack 2 (do not run sdesetup –o upgrade command or the Post-Installation Wizard)
Install the Case-Sensitivity Patch.
- Download the appropriate files to a location other than the ArcSDE installation folder:
  
  sde92-SSCS-patch-sql.exe 3MB
- Double-click the appropriate executable to start the install process.
- When Setup starts, follow the instructions on your screen.
Run Post-Installation Wizard (run a custom setup to include the repository setup, software authorization and create service options) to upgrade the repository, authorize the software with a 9.2 license file and create a new 9.2 service. This can also be done manually using the sdesetup –o upgrade and sdeservice commands.
The upgrade of the ArcSDE repository will do the following:
- - Change the collation of all necessary character fields in the SDE_ and GDB_ tables to match the new default database collation. When the collation of a database is changed, existing data retains the original collation. Only data created subsequent to the change will use the new database default. Upgrading the SDE_ and GDB_ tables will alter the collation and set those columns to use a case-insensitive sort order, required by the geodatabase.
- - Add a new SDE_dbtune table parameter 'COLLATION_NAME'. This parameter is used to set a case-sensitive collation on user-defined fields that store character data. For more information on configuring this parameter, please see the Case Sensitivity in SQL Server Geodatabases whitepaper.
If necessary, create a new 9.2 ArcSDE service.
After performing these steps your geodatabase will have been correctly upgraded to a case-insensitive collation.

For information on configuring support for Case-Sensitive queries & data storage, consult the Case Sensitivity in SQL Server Geodatabases whitepaper.

ArcSDE SDK Install for Windows

Make sure you have write access to the ArcSDE SDK installation folder:.
Download the appropriate files to a location other than the ArcSDE SDK installation folder:
ArcSDE SDK sde92-SSCS-patch-sdk.exe 4 MB
Double-click the appropriate executable to start the install process.
When Setup starts, follow the instructions on your screen.

ESRI Products connecting to ArcSDE (Application and Direct Connect connections).

Windows
- If you connect to ArcSDE 9.2 using ArcGIS 9.2, ArcIMS 9.2 or ArcInfo Workstation 9.2, please follow the steps below to install this Patch to your ArcGIS 9.2, ArcIMS 9.2 or ArcInfo Workstation 9.2 installation folder:
- Before you start, make sure you have write access to the installation folder, then download the zip file to that location:
  
  sde92-SSCS-patch-esri-win.zip 4 MB
- ArcIMS only, stop the ArcIMS Tasker, ArcIMS Monitor, ArcIMS Application Server (in that order).
- Use WinZip to extract the new file from sde92-versionpatch-esri-win.zip to the appropriate installation folder, see list below:
  - ArcGIS Desktop, Server, Engine, Reader, or ArcView 9 - <installation-folder>\arcgis\bin
  - ArcGIS Workstation - <installation-folder>\arcexe9x\bin
  - Depending on the ArcIMS Setup features installed, the SDE client file could be located in one or more of the following locations:
    - C:\Program Files\ArcGIS\bin
    - C:\Program Files\ArcGIS\ArcIMS\IndexBuilder
    - C:\Program Files\ArcGIS\ArcIMS\Metadata\Commands
    - C:\Program Files\ArcGIS\ArcIMS\Server\bin
- ArcIMS only, restart ArcIMS Application Server, ArcIMS Monitor and ArcIMS Tasker (in that order).

How to identify which Patch is installed

Windows

To check for the presence of a Patch, for each file examine the unique identification information provided (right click, properties, version tab, item name, QFE Version). With only a few exceptions, all files modified as part of a Patch distribution can be uniquely identified in this manner. The identification string for this Patch should read:
- CR49864

Patch Updates

Check the Online Support Center periodically for the availability of additional Patches or Service Packs. New information about this Patch will be posted here.

Getting Help

Domestic sites, please contact ESRI Technical Support at 1-888-377-4575, if you have any difficulty installing this Service Pack. International sites, please contact your local ESRI software distributor.

Download ID:1299