SYMPTOM:
When a user selects Project Configuration or browses for attributes in the report editor in Strategy Developer 10.x the following error message appears:
Database Error <>: Execute Query failed. Error type: odbc error. odbc operation attempted: SQLExecDirect. Line 1: Incorrect syntax near 'COLLATE'
CAUSE:
The statement that causes the error is:
CREATE TABLE #SA00010F6CD226E4 (id CHAR(32) COLLATE database_default)
This statement is generated to create temporary tables against the metadata for search queries. This SQL cannot be changed (unlike WareHouse Catalog SQL).
This statement can also be tested on the SQL Server Query Analyzer to see if the user receives the same error message.
ACTION:
This issue can be resolved by changing the compatibility level of the database on SQL Server from 65 to 80, as the setting with compatibility level = 65 errors out on the statement with invalid syntax.
