When upgrading Secret Server to version 8.2.000000 or later, you may receive the following error while upgrading:
"Incorrect syntax near 'PIVOT'". You may need to set the compatibility level of the current database to a higher value to enable this feature.
This indicates that your Secret Server database's compatibility is set to SQL Server 2000. Starting in Secret Server 8.1.000035, the compatibility level must be set to SQL Server 2005 or higher. You can easy change the compatibility level of your Secret Server database by doing the following.
- Restore your database and Secret Server back to the previous version before the attempted upgrade. See the Restoring Secret Server From a Backup Knowledge Base article for more information.
- In SQL Server Management Studio, right click your database and click "Properties".
- In the Properties dialog, select the "Options" page on the left.
- Set the Compatibility level to "SQL Server 2005 (90)" or higher. Setting the compatibility level higher is supported. See the image below for the setting to change.
- Once the compatibility level has been updated, perform the Secret Server upgrade again.
If you do not have a recent backup of Secret Server to restore from in Step 1, please contact support