NIM006734 for No Product Found

Bug ID Number	NIM006734
Submitted	February 9, 2007
Last Modified	October 11, 2024
Applies to	No Product Found
Version found	9.2
Version Fixed	9.3
Status	Fixed

Description

After upgrading to Microsoft SQL Server 2005, the time it takes to open an ArcSDE connection in ArcCatalog or ArcMap is very slow.

Cause

In SQL Server 2000, ArcSDE queries the sysprotects system table to get permission when building the ArcCatalog Table of Contents or any similar operation that generates a list of available datasets.

In SQL Server 2005, many of the system tables, such as sysprotects, have been deprecated in favor of system views. The tables still exist and can be queried, but the performance of the queries may be compromised.

Workaround

A new query has been written to generate a list of datasets. This new query takes advantage of new SQL Server 2005 Transact-SQL operators and functions that improves connection performance.

Follow the steps below:

Ensure that the SQL Server 2005 database compatibility level is 90.

If this database has been upgraded from SQL Server 2000, the compatibility level may still be 80. In SQL Server 2005 Management Studio, right-click the database and click Properties. In the Database Properties dialog box, verify that the compatibility level is 90. If it is not, select SQL Server 2005 (90) from the Compatibility level drop-down list and click OK.

To use the sp_dbcmptlevel stored procedure to modify the compatibility level, execute the following command:
```
Code:
exec sp_dbcmptlevel 'database_name', 90
```
Install ArcSDE 9.2 Service Pack 2. See the link in the Related Information section below.