AI OnAI Off
Cannot resolve the collation conflict in the equal to operation
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.
Edited,
Aug 05, 2015 1:35
I know this is an old thread but I got this exact same issue, discussed here:
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?
Edited,
May 17, 2019 15:10
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.
May 17, 2019 16:25
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.
May 17, 2019 16:34
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!
May 17, 2019 16:38
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.