Cannot resolve sort conflict

Question

The following error message appears.

Unable to resolve collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in equal action.

I only get this when I put this code below in a WHERE clause.

WHERE Region IN (SELECT Token FROM dbo.getParmsFromString(@Region)) 

Now @Region contains all the values ​​from my fields with multiple selections from SSRS.

Below is the code of the function used.

 CREATE FUNCTION [dbo].[getParmsFromString] (@String VARCHAR(MAX)) RETURNS @Parms TABLE ( Token VARCHAR(MAX) ) AS BEGIN IF CHARINDEX(',', @String) != 0 BEGIN ;WITH cte0(Token, List) AS ( SELECT SUBSTRING(@String, 1, CHARINDEX(',',@String,1) - 1) ,SUBSTRING(@String,CHARINDEX(',',@String,1) + 1, LEN(@String)) + ',' UNION ALL SELECT SUBSTRING(List,1,ISNULL(CHARINDEX(',',List,1) - 1,1)) ,SUBSTRING(List,CHARINDEX(',',List,1) + 1, LEN(List)) FROM cte0 WHERE LEN(cte0.List) > 0 ) INSERT INTO @Parms (Token) SELECT Token FROM cte0 OPTION (MAXRECURSION 0) RETURN; END ELSE INSERT INTO @Parms SELECT @String RETURN; END 

Answer 1

Try to change

  RETURNS @Parms TABLE ( Token VARCHAR(MAX) ) 

with

 try changing RETURNS @Parms TABLE ( Token VARCHAR(MAX) COLLATE DATABASE_DEFAULT ) 

and

 WHERE Region IN (SELECT Token FROM dbo.getParmsFromString(@Region)) 

with

 WHERE Region COLLATE DATABASE_DEFAULT IN (SELECT Token FROM dbo.getParmsFromString(@Region)) 

Answer 2

You should definitely check out this link:

http://msdn.microsoft.com/de-de/library/ms179886.aspx

There is also an example of changing the sort in a query.

Answer 3

Usually this type of error occurs when you try to compare data in different regions or when comparing data using a specific encryption with other data using a different encryption. The most likely reason is that their tempdb uses the matching SQL_Latin1_General_CP1_CI_AS , while the database uses the Latin1_General_CI_AS . As a result, temporary objects are created by matching "SQL_Latin1_General_CP1_CI_AS" , and then cannot compare with database objects that use the matching "Latin1_General_CI_AS" .

The simplest fix, as well as one that would recommend running the database on the server that was installed using the "Latin1_General_CI_AS" mapping .

FYI. SQL mappings ( "SQL_Latin1_General_CP1_CI_AS" ) are present in the SQL server for backward compatibility. When working with international data or databases using Unicode and Unicode data, it is recommended that you use Windows mappings ( "Latin1_General_CI_AS" ).

You can change the sorting of the database:

 use master ALTER DATABASE "Your database" COLLATE Latin1_General_CI_AS; SELECT name, collation_name FROM sys.databases; 

and if necessary, you can also change the sorting of the "master" database, that is, rebuild the database, for this, follow the links:

http://msdn.microsoft.com/en-us/library/dd207003(v=sql.100).aspx

http://sqlbuzz.wordpress.com/2011/08/20/how-to-rebuild-master-database-aka-rebuilding-sql-server-2008r2/

but before doing this, back up your entire database.