| Report: Status: Execution failed Error: SQL Generation CompleteQueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42000:468: on SQLHANDLE] ..... Cannot resolve the collation conflict between "SQL_Latin_General_CP1_CI_AS" and "Latin1_General_CI_AS" |
This error occurred on one of our reports after we switched our DW database to a different server.
We are running on MSSQL 2008 R2.
The technical note: TN8850 was the only reference I could find for this issue.
In short, they say that the tempdb, were intermediate tables are created, is using a different collation from the actual warehouse db. They suggest the following action:
ACTION:
This issue must be resolved on the database side. Ensure that temporary tables inherit the current database's collation instead of the tempdb collation. To do this, use a "COLLATE database_default" clause after each char/varchar/nchar/nvarchar column.
At our client we were not able to make any database side changes, so we implemented the following that workaround solved our problem.
Summary:
Change the effected report VLDB settings (or entire project if necessary) to use derived tables instead of true temporary tables. This means that the entire SQL runs within 1 database and does not have to create joins to the tempdb.
Method:
- Edit the report (or cube) that gives the error
- Go to menu: Data >> VLDB Properties...
- Tables >> Intermediate Table Type
- Un tick "Use default inherited value"
- Select "Derived table" radio button
- Save and Close
If all of your reports give this issue, it is possible to set the VLDB settings for the entire Database Instance.
Please note that this is a workaround and I don't suggest this as a permanent solution.
No comments:
Post a Comment