Collation conflict when running SQL database based report

[petejigsaw]

I am working on a report that uses two tables from within the same SQL 2005 database.

The database is set to CI (case insensitive), but when I add two of the tables to the report, link them on fields I know should work, I get this error:

Failed to retrieve data from the database.
Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_CI_AS" in the equal to operation. [Database Vendor Code: 468]

Can anyone help explain why this is occuring and how I go about resolving this issue?

Many thanks

 

[RTag]

You can resolve the problem by instructing the SQLEngine to use a specific collation. For example:
select ... from Table1 t1
inner join Table2 t2
on (t1.ID= t2.ID COLLATE SQL_Latin1_General_CP1_CI_AS)

You can replace SQL_Latin1_General_CP1_CI_AS with the right collation or use the database default

If you are joining the tables in the Database expert you will need to change the report to use a stored procedure , command or view.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.