Collation issue

Martin Öhman

Vote:

Hi, I have an existing EPiServer 6.0.530.0 site and I tried to add EPiServer Community 4.0.517.255 by using "Install Community on existing site" in the deployment center.

Most of the stuff seems to be working, but some db calls fail with the message "Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict".

For example.

[SqlException (0x80131904): Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
   System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) +213
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +594
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +112
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6281668
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6282737
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +211
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
   EPiServer.Common.Data.DatabaseHandler.GetReader(Boolean inTransaction, String sp, CommandType commandType, Object[] args) +61
   EPiServer.Common.Data.DatabaseHandler.GetReader(Boolean inTransaction, String sp, Object[] args) +24
   EPiServer.Community.Calendar.Data.CalendarFactory.GetEvents(Calendar calendar, EventPublishState publishState, Int32 page, Int32 pageSize, DateTime fromDate, DateTime toDate, Boolean singleOccurrencesOnly, EventSortOrder[] sortOrder) +795

[FrameworkException: Could not get events for calendar with id 2.]
   EPiServer.Community.Calendar.Data.CalendarFactory.GetEvents(Calendar calendar, EventPublishState publishState, Int32 page, Int32 pageSize, DateTime fromDate, DateTime toDate, Boolean singleOccurrencesOnly, EventSortOrder[] sortOrder) +1293

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +1255
   System.Delegate.DynamicInvokeImpl(Object[] args) +115
   EPiServer.Common.Cache.CacheHandler.GetCachedObjectImplementation(Delegate cacheObjectGetter, Object[] getterArgs, Nullable`1 cacheTimeSpan, NotFoundInCacheAnnouncer notFoundInCacheAnnouncer, String[] key) +450
   EPiServer.Common.Cache.CacheHandler.GetCachedObject(Delegate cacheObjectGetter, Object[] getterArgs, String[] key) +74
   EPiServer.Community.Calendar.CalendarHandler.GetEvents(Calendar calendar, EventPublishState publishState, Int32 page, Int32 pageSize, DateTime fromDate, DateTime toDate, Boolean singleOccurrencesOnly, Int32& totalItems, EventSortOrder[] sortOrder) +1171
   EPiServer.Community.Calendar.CalendarHandler.GetEvents(Calendar calendar, EventPublishState publishState, Int32 page, Int32 pageSize, DateTime fromDate, DateTime toDate, Int32& totalItems, EventSortOrder[] sortOrder) +86

   EPiServer.Templates.RelatePlus.Pages.ClubEvent.BindMostRecentClubEvents(Int32 page) in <Website path>\Templates\RelatePlus\Pages\ClubEvent.aspx.cs:220
   EPiServer.Templates.RelatePlus.Pages.ClubEvent.OnLoad(EventArgs e) in <Website path>\Templates\RelatePlus\Pages\ClubEvent.aspx.cs:137
   System.Web.UI.Control.LoadRecursive() +66
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2428

The collation of both the episerver and the episerver community databases are Latin1_General_CI_AI

Any ideas?

Thanks.

Martin

#42827

Sep 02, 2010 14:55

Magnus Rahl

Vote:

Not a solution, just an observation: I don't think that the error is the result of a collaction conflict between the two databases, it's a conflict between the call and the database. I have seen something similar once when I was running a query on one database that was created on a database with a different collation. I would try to figure out what query the failing method call results in.

#42845

Sep 02, 2010 20:18

Martin Öhman

Vote:

Thanks for your reply.

The stored procedure called creates some temporary tables.

I foud this article that seems to explain the problem: http://foxtricks.blogspot.com/2008/07/how-to-properly-create-temp-tables-in.html

The collaction of the server im using is SQL_Latin1_General_CP1_CI_AS.

I really don't want to change any Community sql and the only other solution I can think of is to make sure both databases, their tables & the server has the same collation.

That seems to ba a hassle (especially since i have a copy of the EPiServer database in production already), does anyone have any other ideas?

#42858

Edited, Sep 03, 2010 9:19

Dung Le

Vote:

Hello,

Fix problem with collation when deploy data of community.

Here is work around that I tried and succeed :)

- On your source server, generate script of community database with following option (select database, Task -> Generate Scripts):

o Generate script for dependent objects: true o Script Collation: True (Important)

o Script Data: True (used to generate data)

o Script full-text indexes: true if you want o Script Triggers: true

- On your destination server, create new database with any name, open the script file on previous step

- Make sure you use the newly created database, execute the script

- Stop your community site (via IIS)

- Rename your old community database

- Rename your new database to your original database name

Hope this helps

/Dung Le

#44370

Oct 11, 2010 6:55

Vibeke Randby

Vote:

Hi,

Did you manage to solve this problem? I'm having the same problem here. I've tried to alter the collation on both cms and community databases to match the collation on the sql server, with no luck so far. I also tried to create a new database as described above.

Vibeke

#46782

Jan 04, 2011 16:10

Martin Öhman

Vote:

Unfortunatly not.

Martin

#46783

Jan 04, 2011 18:01

Vibeke Randby

Vote:

My solution was to move the community database to another SQL server instance which have the same collation as the database. The problem originally occured after I restored the database onto a new SQL Server instance.

Vibeke

#46799

Jan 05, 2011 10:11

Vote:

We had the same problem;

The problem was that the spEPiServerCommunityCalendarGetEvents made use of the DB server local temp table (which had another collation). What we did was the following inside of the sp:

Remove the "#" (which means create as temptable) in the beginning of these tablenames:

#tmptblEPiServerCommunityEvents
#recurrentEvents

The difference is that we write the tables to the current DB (the community DB) which has the right collation. This was a tweak for our dev env. the tables get dropped in the end of the SP.

#64461

Edited, Dec 20, 2012 16:52

Mark A

Vote:

Thanks Andreas, your tweak of the stored procedure resolved this for me.

#65662

Feb 05, 2013 9:41