Cannot resolve the collation conflict in the equal to operation

Vote:
 

I have , updated to latest EPi version today and when deploying to Azure I get an error. If you hurry you might catch this detailed error msg at my blog :-)

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation.

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

  <%@ page language="C#" debug="true" %>

or:

2) Add the following section to the configuration file of your application:


   
       
   



Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:

 

[SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation.]

   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1787814

   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5341674

   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +546

   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1693

   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +869

   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +413

   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163

   EPiServer.Data.SchemaUpdates.<>c__DisplayClassa.b__8() +94

 

[DataException: Failed to update database during execution of statement 'CREATE PROCEDURE [dbo].[netNotificationMessageGetForRecipients]

        @ScheduledBefore DATETIME2 = NULL,

        @Recipients dbo.StringParameterTable READONLY

AS

BEGIN

        SELECT

               pkID AS ID, Recipient, Sender, Channel, [Type], [Subject], Content, Sent, SendAt, Saved, [Read], Category

               FROM

                       [tblNotificationMessage] AS M INNER JOIN @Recipients AS R ON M.Recipient = R.String

               WHERE

                       Sent IS NULL AND

                       (SendAt IS NULL OR

                       (@ScheduledBefore IS NOT NULL AND SendAt IS NOT NULL AND @ScheduledBefore > SendAt))

                                     

               ORDER BY Recipient

END

']

   EPiServer.Data.SchemaUpdates.<>c__DisplayClassa.b__8() +204

   EPiServer.Data.Providers.<>c__DisplayClass35`1.b__34() +132

   EPiServer.Data.Providers.NoRetriesPolicy.Execute(Func`1 method) +9

   EPiServer.Data.Providers.SqlDatabaseHandler.ExecuteTransaction(Func`1 action) +124

   EPiServer.Data.SchemaUpdates.ScriptRunnerImpl.ExecuteScript(IDatabaseHandler databaseHandler, StreamReader stream) +242

   EPiServer.Data.SchemaUpdates.<>c__DisplayClass1.b__0() +164

   EPiServer.Data.Providers.<>c__DisplayClass32.b__31() +10

   EPiServer.Data.Providers.<>c__DisplayClass35`1.b__34() +132

   EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute(Func`1 method) +45

   EPiServer.Data.Providers.SqlDatabaseHandler.ExecuteTransaction(Func`1 action) +124

   EPiServer.Data.Providers.SqlDatabaseHandler.ExecuteTransaction(Action action) +90

   EPiServer.Data.SchemaUpdates.ScriptRunnerImpl.ExecuteScripts(IDatabaseHandler databaseHandler, IEnumerable`1 streams) +87

   EPiServer.Data.SchemaUpdates.ScriptExecutorImplementation.ExecuteEmbeddedZippedScripts(String connectionString, Assembly assembly, String resourcePath) +425

   EPiServer.Data.DatabaseVersionValidator.Update(ConnectionStringSettings connectionStringSettings) +57

   EPiServer.Data.SchemaUpdates.DatabaseSchemaManagerImplementation.EnureDatabaseSchemaVersion(ConnectionStringsSection connectionStrings, Boolean automaticSchemaUpdatesEnabled) +953

   EPiServer.Data.DataInitialization.ValidateDatabaseSchema(InitializationEngine context) +142

   EPiServer.Data.DataInitialization.Initialize(InitializationEngine context) +25

   EPiServer.Framework.Initialization.<>c__DisplayClass5.b__4() +19

   EPiServer.Framework.Initialization.ModuleNode.Execute(Action a, String key) +43

   EPiServer.Framework.Initialization.ModuleNode.Initialize(InitializationEngine context) +80

   EPiServer.Framework.Initialization.InitializationEngine.InitializeModules() +173

 

[InitializationException: Initialize action failed for Initialize on class EPiServer.Data.DataInitialization, EPiServer.Data, Version=8.11.0.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7]

   EPiServer.Framework.Initialization.InitializationEngine.InitializeModules() +441

   EPiServer.Framework.Initialization.InitializationEngine.Initialize(HostType hostType) +116

   EPiServer.Framework.Initialization.InitializationModule.Initialize(HostType hostType) +447

   EPiServer.Framework.Initialization.InitializationModule.FrameworkInitialization(HostType hostType) +83

   EPiServer.Global..ctor() +76

   ASP.global_asax..ctor() +5

 

[TargetInvocationException: Exception has been thrown by the target of an invocation.]

   System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) +0

   System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) +113

   System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) +232

   System.Activator.CreateInstance(Type type, Boolean nonPublic) +83

   System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark) +1122

   System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) +128

   System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture) +18

   System.Web.HttpRuntime.CreateNonPublicInstance(Type type, Object[] args) +60

   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +260

   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +296

 

[HttpException (0x80004005): Exception has been thrown by the target of an invocation.]

   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9930872

   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101

   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +254

I think I got the hang of what has happend, EPi VS addon created the database SQL_Latin1_General_CP1_CI_AS, then I just out of old habit didn't check that and put Finnish_Swedish_CI_AS when creating an empty database in Azure.

I would still consider this a bug in the update scripts.

I'll post an update if I can solve it without migrating to a new Azure database.

#131760
Aug 04, 2015 23:38
Vote:
 

Nah... Created a new Azure database with SQL_Latin1_General_CP1_CI_AS as database collation and migrated to it using SQLAzureMW v5.15.6. Then I got the startup DB upgrade to finish and I could get the site back up.

In the migration process I found problems with this TSQL:

Cannot resolve the collation conflict between "Latin1_General_BIN2" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
CREATE PROCEDURE [dbo].[ChangeNotificationDequeueString]
    @connectionId uniqueidentifier,
    @maxItems int
as
begin
    begin try
        begin transaction
        declare @processorId uniqueidentifier
        declare @processorStatus nvarchar(30)
        declare @processorStatusTable table (ProcessorId uniqueidentifier, ProcessorStatus nvarchar(30), LastConsistentDbUtc datetime)
        insert into @processorStatusTable (ProcessorId, ProcessorStatus, LastConsistentDbUtc)
        exec ChangeNotificationAccessConnectionWorker @connectionId, 'String'
        select @processorId = ProcessorId, @processorStatus = ProcessorStatus
        from @processorStatusTable
        if (@processorStatus = 'valid')
        begin
            if exists (select 1 from tblChangeNotificationQueuedString where ConnectionId = @connectionId)
            begin
                raiserror('A batch is already pending for the specified queue connection.', 16, 1)
            end
            declare @result table (Value nvarchar(450) collate Latin1_General_BIN2)
            insert into @result (Value)
            select top (@maxItems) Value
            from tblChangeNotificationQueuedString
            where ProcessorId = @processorId
              and ConnectionId is null
            order by QueueOrder
            update tblChangeNotificationQueuedString
            set ConnectionId = @connectionId
            where ProcessorId = @processorId
              and Value in (select Value from @result)
            select Value from @result
        end
        commit transaction
    end try
    begin catch
        declare @msg nvarchar(4000), @sev int, @stt int
        select @msg = ERROR_MESSAGE(), @sev = ERROR_SEVERITY(), @stt = ERROR_STATE()
        rollback transaction
        raiserror(@msg, @sev, @stt)
    end catch
end

Collation is important anyhow so maybe a setting should be part of the New project wizard. Also EPi should try to never specify it using "collate" in their scripts.

#131762
Edited, Aug 05, 2015 1:35
Vote:
 

This has been submitted as a bug now, #129075.

#133034
Aug 25, 2015 13:11
Vote:
 

I know this is an old thread but I got this exact same issue, discussed here:

https://world.episerver.com/forum/developer-forum/Developer-to-developer/Thread-Container/2019/2/missing-stored-procedures-in-database/

Some stored procedures is missing and i can't create them because if this collation mismatch.

I searched for bug# 129075 but couldn't find anything....

Was the issue resolved by creating a new database and migrate the old database content to it?

#204064
Edited, May 17, 2019 15:17
Vote:
 

Which version is it happening in?

#204071
May 17, 2019 16:21
Vote:
 

The site is now on 11.11.2.

But if i recall it correctly we noticed the issue when upgrading to EPiServer 10.x.x. The site is working but there have been some issues every time we upgrade EPiServer. Everytime we need to change the collation on some columns and add the missing stored procedures manually.

#204072
May 17, 2019 16:25
Vote:
 

I would file a new support ticket and refer to the information in this thread. I haven't seen the error since but I only saw it once and on a db created in SQL Azure.

#204073
May 17, 2019 16:34
Vote:
 

The same issue is present in multiple databases, all were originally created in an on prem-enviroment and migrated to Azure Sql.

Sure I will create a support ticket referencing this thread. Thanks for your help!

#204074
May 17, 2019 16:38