Collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Modern_Spanish_CI_AS” in the equal to operation
I was creating this SQL function from SQL SERVER 2008 R2 to WINDOWS AZURE, but I don’t how to resolve this problem.
Msg 468, Level 16, State 9, Procedure GetObjectivesByTest, Line 69
Cannot resolve the collation conflict between
“SQL_Latin1_General_CP1_CI_AS” and “Modern_Spanish_CI_AS” in the equal
Storing a Dictionary<int,string> or KeyValuePair in a database executing stored requests in a table and getting the values into a variable SQL Server: What's the limit on number of UNIONs? What is the difference between SET xact_abort ON and try/catch block with Transaction handling in sqlserver 2005? SSRS - Group_Concat Equivalent using an Expression? Locking Row in SQL 2005-2008
CREATE FUNCTION [dbo].[GetObjectivesByTest](@testId smallint) RETURNS @res TABLE ( -- Add the column definitions for the TABLE variable here ObjectiveId smallint NOT NULL, Name nvarchar(50) NOT NULL, Expectations nvarchar(400) NULL, [Level] nvarchar(5) NOT NULL, ParentId smallint NULL, LearningSystem nvarchar(30) NULL, [Rank] tinyint NULL ) AS BEGIN DECLARE @string VARCHAR(MAX) SELECT @string = OBJECTIVES FROM TESTS WHERE TestId = @testId DECLARE @temp TABLE ( ColumnA NVARCHAR(50), ColumnB NVARCHAR(500), ID INT IDENTITY(1,1) ) INSERT INTO @temp (ColumnA, ColumnB) VALUES ('', @string) DECLARE @idx INT, @cnt INT SET @idx = 1 SELECT @cnt = COUNT(*) FROM @temp DECLARE @SplitStr nvarchar(1000), @SplitChar nvarchar(5), @Columns VARCHAR(50) SET @SplitChar = ',' WHILE @idx <= @cnt BEGIN SELECT @SplitStr = ColumnB FROM @temp WHERE id = @idx DECLARE @RtnValue table ( ColumnName VARCHAR(50), Data VARCHAR(50) ) Declare @Count int Set @Count = 1 While (Charindex(@SplitChar,@SplitStr)>0) Begin Insert Into @RtnValue (ColumnName,Data) Select @Columns, Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1))) Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr)) Set @Count = @Count + 1 End Insert Into @RtnValue (ColumnName,Data) Select @Columns,Data = ltrim(rtrim(@SplitStr)) SET @idx = @idx + 1 END INSERT @RES // here is appointing the error SELECT C.* FROM Objectives AS C INNER JOIN OBJECTIVES AS B ON (C.ParentId = B.ObjectiveId) INNER JOIN OBJECTIVES AS A ON (B.ParentId = A.ObjectiveId) where C.Rank = 3 AND B.Rank = 2 AND A.Rank = 1 AND A.LearningSystem + ' ' + A.Level + '.' + C.Level IN (SELECT Data FROM @RtnValue) RETURN END
I didn’t have idea about this problem, how can I fix that incompatibility.
Thanks in advance.
3 Solutions collect form web for “Collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Modern_Spanish_CI_AS” in the equal to operation”
You have a collation mismatch between the database collation (@RtnValue.Data) and the collation used in Objectives.LearningSysten.
Quickest solution may be to explicitly declare the collation in @RtnValue:
DECLARE @RtnValue table ( ColumnName VARCHAR(50), Data VARCHAR(50) COLLATE [insert required collation name] )
This is a quick fix, however, you should check correct use of collations on database and table column level.
Collation defines how SQL Server compares string values, and is specified at various levels within SQL Server:
- Server default collation: This is specified as an option when installing SQL Server, and defines the collation that will be used for any new databases, as well as that of the master and temp databases.
- Database default collation: This is specified when creating a new database. If not specified, the server default collation will be used. This collation is used for any string valued columns (CHAR, VARCHAR, NCHAR, NVARCHAR) created in the database. This collation is also used as the default for any string valued variables and string columns in table-valued variables.
- Column collation: This is specified at column level, and specifies the collation used for the specific column.
Some things you should bear in mind as well:
- When restoring a database onto a new server, the server will not convert the collation for the database to that of the server.
- The default collation used varies in different contexts: Table-valued variables and variables in T-SQL use the database default, and TempDB columns use the server default.
You cannot implicitly compare string values with different collations. While the right thing to do would be to use the correct collation across the board, there are a few simple workarounds. Here are your options, in increasing order of complexity:
If this is an isolated query in which a temp table string column is being compared with a corresponding value in the database, or you’re in a hurry and just want to get it working, you can specify the collation in the
WHEREclause. You will need to do this wherever you compare string values in the database with local variables in T-SQL queries:
WHERE C.Rank = 3 AND B.Rank = 2 AND A.Rank = 1 AND A.LearningSystem + ' ' + A.Level + '.' + C.Level COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT Data COLLATE SQL_Latin1_General_CP1_CI_AS FROM @RtnValue)
Your next option, and probably the best solution, is to match the database default collation and the collation used in all string columns in the database. Altering the database collation is as simple as calling
ALTER DATABASE MyDB COLLATE SQL_Latin1_General_CP1_CI_AS, as described in the MSDN Technet article Set or Change the Database Collation. What it won’t do for you, however, is to change the collation of the columns in the database. You can generate a script to do this for you, however, using the system tables. I do not have SQL Server on my machine here, so I haven’t been able to test this, but this will give you the general idea. Run the script, copy the results into the SQL pane and then run that.
WITH cte AS (SELECT o.name AS TableName, c.name AS ColumnName, t.name AS TypeName, c.max_length AS MaxLen FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id INNER JOIN sys.types t ON t.system_type_id = c.system_type_id WHERE o.type = 'U' AND t.name IN ('char', 'nchar', 'varchar', 'nvarchar')) SELECT 'ALTER TABLE ' + TableName + ' ALTER COLUMN ' + ColumnName + ' ' + TypeName + '(' + CAST(CASE WHEN SUBSTRING(TypeName, 1, 1) = 'n' THEN MaxLen/2 ELSE MaxLen END AS VARCHAR) + ') COLLATE SQL_Latin1_General_CP1_CI_AS' FROM cte
Note that with this solution, you will still need to specify the collation for any columns used in temporary tables, whether these are in the context of a stored procedure or a raw T-SQL command. However, this is good practise, because if you deploy this database to a customer who already has their own database server and they wish to use the same server, you cannot expect them to have to change their server’s default collation.
- Finally, you can change the server collation as well to match that of the database and all of the columns. This is a pain in the butt, but can be done using the original setup media set, as described on the MSDN Technet site at Setting and Changing the Server Collation on the MSDN Technet site.
These are ugly. I know of two ways to resolve this and neither are all that elegant:
Change the collation of one of the databases to match the other: http://msdn.microsoft.com/en-us/library/ms175835(v=sql.105).aspx
Or change the collation of each of the columns in your query/table to match the destination database: http://msdn.microsoft.com/en-us/library/ms190920(v=sql.105).aspx