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.
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.
- Disconnect all users from ArcSDE and restart the ArcSDE service.
- Create a new child version of default and name it 'state_0'.
- 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'
- Run the compress command.
- Delete the version 'state_0'.
- Run the compress command again.
- Check the SDE.VERSIONS table to make sure the state-id for DEFAULT has returned to zero.