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.
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:
Code: 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.