Bug: Failed to compress database. Invalid SQL syntax [SDE.default]


** This issue is addressed in the ArcSDE 9.1 Service Pack 1 (SP1) **

Errors occur with ArcSDE 9.1 for SQL Server when using Compress command in ArcCatalog:

"Failed to compress the database.
Invalid SQL syntax [sde.DEFAULT]"

The following error is displayed when using the 'sdeversion -o compress' command:

"Error: Invalid SQL syntax (-42).
Error: Unable to compress state tree.
Invalid SQL syntax"


The error occurs when there are greater than 150 states to be trimmed to base.


** This issue is addressed in ArcSDE 9.1 Service Pack 1 (SP1).

Click on the link in Related Information below to download the Service Pack, if necessary. **

It is advisable to have a full backup of the database before following this procedure in case it becomes necessary to undo the changes resulting from it. After following this procedure, compress will perform normally unless/until compress needs to trim >150 states to base.
  1. Disconnect all users from ArcSDE and restart the ArcSDE service.
  2. Create a new child version of default and name it 'state_0'.
  3. Connect to the SDE database via SQL Profiler as the SDE user and run the following command:

    UPDATE sde_versions SET state_id = 0 WHERE name = 'state_0'

  4. Run the compress command.
  5. Delete the version 'state_0'.
  6. Run the compress command again.
  7. Check the SDE.VERSIONS table to make sure the state-id for DEFAULT has returned to zero.

Related Information