Problem: Can no longer connect after upgrading to ArcSDE 9.2 SP2 or higher on SQL Server 2005
After upgrading geodatabases on SQL Server 2005 to ArcSDE 9.2 Service Pack 2 or higher, connections from ArcGIS fail, sometimes intermittently.
ArcSDE 9.2 Service Pack 2 fixed a problem with slow connections to SQL Server 2005 geodatabases. This fix uses a new Transact-SQL Operator, CROSS APPLY. To use this operator, database compatibility level must be set to a minimum of 90. Databases that were upgraded from SQL Server 2000 may still have a compatibility level of 80.
This behavior may be seen on databases that have been upgraded from SQL Server 2000 to SQL Server 2005 or higher, with ArcSDE 9.2 SP2 or higher. Upgrading a SQL Server database does not change the compatibility level.
Solution or Workaround
Upgrade the compatibility level of the SQL Server databases to 90 or higher. Do this either through the Database Properties dialog box in Management Studio or by using the sp_dbcptlevel stored procedure.
- In SQL Server Management Studio, right-click the database to be upgraded and click Properties. On the Database Properties dialog box, select the Options page. Select SQL Server 2005 (90) from the Compatibility level drop-down list and click OK.
(When using SQL Server 2008, select Compatibility level 100.)
- To use the sp_dbcmptlevel stored procedure, open a query window and type and execute the following command, substituting the name of the database to be upgraded for @database_name:
exec sp_dbcmptlevel 'database_name', 90