collation settings for reportserver database

Question

collation settings for reportserver database

pshaship

SSCertifiable

Points: 6125
More actions
April 19, 2010 at 4:24 pm

#223546

Hi,
What are the collation settings for ReportServer & ReportServerTempDb?
I know that the Reporting Services Configuration Manager sets the collation settings as “ Latin1_General_CI_AS_KS_WS” by default. But our ReportServer & ReportServerTempDb has the collation settings as SQL_Latin1_General_CP1_CI_AS.
I did NOT understand why the default collation settings has chnaged for ReportServer & ReportServerTempDB and now we are getting the below error when the report runs:
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pshaship SSCertifiable Points: 6125 More actions · Answer 1

Hi,

Both the System & ReportServer & ReportServerTempDB have same collation settings SQL_Latin1_General_CP1_CI_AS. The reports are working fine but when reporting services backgorund process runs, I'm seeing the collation conflict as below

ERROR: Sql Error in CleanExpiredSessions: System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

But here system & reportserver database have same collations, then how come collation conflict comes into picture?

Thanks

nelsonj-902869 SSCarpal Tunnel Points: 4666 More actions · Answer 2

pshaship (4/22/2010)
Hi,
ERROR: Sql Error in CleanExpiredSessions: System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
But here system & reportserver database have same collations, then how come collation conflict comes into picture?
Thanks

I believe that "somewhere" in your CleanExpiredSessions proc you are referencing a table that has a column with a different collation, hence the conflict. Are there hardcoded collation references in the proc? I was burned by this in 5 of our procs from an outside vendor. Make sure that if your db is defaulted to SQL collation that all the columns have been set to that collation as well, especially if temp tables are being built in the proc using a non-default collation.