Situation: Dnn 4.5.5, Store 2.01.00, SQL2000 (collation Slovak CI AS)

New installation of Store module gets following error:

Failure - SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation. …

As result the Store_Cart_PurgeCarts stored procedure is not created. SQL server has collation settings: Slovak_CI_AS. I noticed datediff() function in procedure. Maybe it is date- time format related problem? Please help.

Jan

Here is excerpt with error from install log:

StartJob Begin Sql execution

Info Executing 01.00.00.SqlDataProvider

StartJob Start Sql execution: 01.00.00.SqlDataProvider file

Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for 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.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo.Store_Cart_PurgeCarts @PurgeDate datetime AS SET NOCOUNT ON DECLARE @OldCarts table (CartID nvarchar(50)) DECLARE @ErrorCode int SELECT @ErrorCode = 0 BEGIN TRANSACTION INSERT INTO @OldCarts SELECT CartID FROM dbo.Store_Cart WHERE UserID IS NULL AND datediff(day, @PurgeDate, DateCreated) < 0 -- Check for errors SELECT @ErrorCode = @@error IF (@ErrorCode <> 0) GOTO Cleanup DELETE FROM dbo.Store_CartItems WHERE CartID IN (SELECT CartID FROM @OldCarts) -- Check for errors SELECT @ErrorCode = @@error IF (@ErrorCode <> 0) GOTO Cleanup DELETE FROM dbo.Store_Cart WHERE CartID IN (SELECT CartID FROM @OldCarts) -- Check for errors SELECT @ErrorCode = @@error Cleanup: IF (@ErrorCode <> 0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 

EndJob End Sql execution: 01.00.00.SqlDataProvider file

Hi Gilles,

Hi Gilles,

Thank you for your suggestion, I have done some tests myself already. Here is the result:

First, Slovak localization of SQL server does not exist. I use (and my ISP too) the English version. In my test environment I am using the default collation SQL_Latin1_General_CP1_CI_AS, but my ISP is using Slovak_CI_AI collation. In my test environment works everything fine (with default collation) therefore I made the tests with two different databases (for sure that the problem is reproducible) on external server of my ISP.

Second, I scripted the stored procedure Store_Cart_PurgeCarts from my internal SQL server and used that script for testing. I found that source of problem is DELETE query on lines 624 and 633 in 01.00.00.SqlDataProvider file.

Solution:
I edited those two lines as follows (bold text is added):

DELETE FROM dbo.Store_CartItems WHERE CartID collate Slovak_CI_AI IN (SELECT CartID FROM @OldCarts)

Query with the above correction runs successfully.

I have no information about design and structure of Store project (and I am too busy to drill through source), but I see here something strange: CartID collation is different from database default collation (tempdb). Why?

I checked that tempdb and dnn databases have the same collation.

Jan