Tuesday, July 3, 2012

Collation Conflict Error

We got the following error at one of our clients:

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:

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.

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.
  1. Edit the report (or cube) that gives the error
  2. Go to menu: Data >> VLDB Properties...
  3. Tables >> Intermediate Table Type
  4. Un tick "Use default inherited value"
  5. Select "Derived table" radio button
  6. 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