collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Question

collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Mani-584606

SSChampion

Points: 11788
More actions
April 14, 2010 at 2:17 am

#156867

Hi,
We have sql server 2005 reporting services on server A & its databases on Server B. I'm seeing the below error message in the log files
ReportingServicesService!dbcleanup!4!4/14/2010-00:02:31:: e 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.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()
ReportingServicesService!library!4!4/14/2010-00:02:31:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
ReportingServicesService!dbcleanup!4!4/14/2010-00:12:31:: e 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
After analyzing, I came to know that ReportServer & ReportServerTembDB have the collations settings as SQL_Latin1_General_CP1_CI_AS
But we should have the collation settings as Latin1_General_CI_AS_KS_WS for ReportServer & ReportServerTembDB right?
I donot know why the previous DBA set the collation settings for ReportServer & ReportServerTembDB as SQL_Latin1_General_CP1_CI_AS
Please advice what steps I can take now
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Samoin SSC Enthusiast Points: 123 More actions · Answer 1

Hi,

Were you able to resolve the issue? I'm facing exactly the same problem and trying to resolve it. Please post the steps you took to resolve the issue.

Thanks

homebrew01 SSC Guru Points: 55539 More actions · Answer 2

If the issue is in SQL code, you can use COLLATE to convert one to the other:

select * from DB_CollationA..TableA A

join DB_CollationB..TableB B on A.Fld1 = B.Fld1 collate Latin1_General_BIN

Samoin SSC Enthusiast Points: 123 More actions · Answer 3

I was able to resolve this issue and below are the steps. Hopefully it will be helpful to others

-- My collation issues were with the ReportServer and ReportServerTempDB databases. I had to drop the ReportServerTempDB database and re-create it. After that I assigned the proper permissions to RSExecRole and restarted the IIS and Reporting Services and it worked.

lanna.jones Valued Member Points: 60 More actions · Answer 4

Could you explain what you mean by changing the permissions to the RSExecRole?? I'm having the same issue and havne't been able to get this to work. Thank you in advance. If its easier my email is lanna.jones@gcmchealth.com

marygzaudtke Valued Member Points: 65 More actions · Answer 5

USE [ReportServerTempDB]

GO

/****** Object: Schema [RSExecRole] Script Date: 02/04/2013 13:45:30 ******/

CREATE SCHEMA [RSExecRole] AUTHORIZATION [RSExecRole]

GO

philcart SSC-Forever Points: 47794 More actions · Answer 6

marygzaudtke (2/4/2013)
USE [ReportServerTempDB]
GO
/****** Object: Schema [RSExecRole] Script Date: 02/04/2013 13:45:30 ******/
CREATE SCHEMA [RSExecRole] AUTHORIZATION [RSExecRole]
GO

If you use the Reporting Services Configuration Manager to create the new databases, then the permissions would have been set at creation time.

Did you lose all your catalog items and have to re-deploy? RSScripter is a great little tool that can script out all the Report Server items to .rss scripts that allow you to re-create everything.

--------------------
Colt 45 - the original point and click interface