collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » collation conflict between...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Mani-584606

Posted Wednesday, April 14, 2010 2:17 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797

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

Post #902926

Samoin

Posted Friday, May 07, 2010 8:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:02 AM
Points: 5, Visits: 139

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

Post #918038

homebrew01

Posted Friday, May 07, 2010 12:58 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,554, Visits: 7,212

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

Post #918297

Samoin

Posted Saturday, May 08, 2010 8:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:02 AM
Points: 5, Visits: 139

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.

Post #918502

lanna.jones

Posted Monday, November 19, 2012 2:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 01, 2013 1:25 PM
Points: 2, Visits: 14

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

Post #1386590

marygzaudtke

Posted Monday, February 04, 2013 1:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 8:06 AM
Points: 1, Visits: 2

USE [ReportServerTempDB]
GO

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

Post #1415469

philcart

Posted Tuesday, February 05, 2013 4:56 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 2,692, Visits: 1,075

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.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club

Post #1415743

« Prev Topic | Next Topic »

Permissions