Collation error

Hello Sirs,
I am facing issue conflict between collation . I have set Server/master database/ User database collation set to SQL_Latin1_General_CP1_CS_AS. But while loading the data from third party applcation throw me error . I have no idea i checked all columns of database table and it is set to default collation SQL_Latin1_General_CP1_CS_AS.

> Cannot load config : Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CI_AI" in CASE operator occurring in SELECT statement column 5.
> 
> System.Exception: Cannot load config : Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CI_AI" in CASE operator occurring in SELECT statement column 5.
>    at Server.Loader.Loader._LoadConfig(XElement configXml)
>    at Server.Loader.Loader._LoadConfig(String fileName)
>    at Server.Loader.Loader.LoadFile(ProcessParameter processParameter)
1 Like

Hi there. Clearly the Latin1_General_CI_AI collation is coming from somewhere. If not coming from a column being used in that CASE expression, then it's possible it is explicitly set using a COLLATE clause in that expression. Can you please update the question to include the query being executed. It is possible that the "column" with the Latin1_General_CI_AI collation is in a DMV or some other system catalog view, and not a column in your user database. But without seeing the query, there is no way to move forward on this.

1 Like

I am unable to update the Original post below are the executed scripts

---- LIST ALL THE COLLATION OF TABLES

 USE UserDB
    GO
    SELECT s.name as 'Schema_Name', t.name as Table_Name,
    c.name AS Column_Name,
    c.collation_name AS Collation
    FROM sys.schemas s
    INNER JOIN sys.tables t
    ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c
    ON c.object_id = t.object_id
    WHERE collation_name is not null
    ORDER BY Column_Name

---CHANGE COLLATION WILL CHANGE THE MAXIUM COLLATION SOME REMAIN SO FIX MANUAL BY NEXT METHOD

CREATE PROCEDURE CHANGE_COLLATION
    AS
    BEGIN
    DECLARE @collate nvarchar(100);
    declare @schema nvarchar(255);
    DECLARE @table nvarchar(255);
    DECLARE @column_name nvarchar(255);
    DECLARE @column_id int;
    DECLARE @data_type nvarchar(255);
    DECLARE @max_length varchar(100);
    DECLARE @row_id int;
    DECLARE @sql nvarchar(max);
    DECLARE @sql_column nvarchar(max);

    SET @collate = 'SQL_Latin1_General_CP1_CS_AS';

    DECLARE tbl_cursor CURSOR FOR SELECT (s.[name])schemaName, (o.[name])[tableName]
    FROM sysobjects sy 
    INNER JOIN sys.objects  o on o.name = sy.name
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE OBJECTPROPERTY(sy.id, N'IsUserTable') = 1

    OPEN tbl_cursor FETCH NEXT FROM tbl_cursor INTO @schema,@table

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE tbl_cursor_changed CURSOR FOR
            SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
                , c.name column_name
                , t.Name data_type
                , c.max_length
                , c.column_id
            FROM sys.columns c
            JOIN sys.types t ON c.system_type_id = t.system_type_id
            LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
        WHERE c.object_id like OBJECT_ID(@schema+'.'+@table)
        ORDER BY c.column_id


        OPEN tbl_cursor_changed 
         FETCH NEXT FROM tbl_cursor_changed
        INTO @row_id, @column_name, @data_type, @max_length, @column_id



        WHILE @@FETCH_STATUS = 0
        BEGIN
        IF (@max_length = -1) SET @max_length = 'MAX';
            IF (@data_type LIKE '%char%')
            BEGIN TRY
                SET @sql = 'ALTER TABLE ' +@schema+'.'+ @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
                print @sql
                EXEC sp_executesql @sql
            END TRY
            BEGIN CATCH
              PRINT 'ERROR:'
              PRINT @sql
            END CATCH

            FETCH NEXT FROM tbl_cursor_changed
            INTO @row_id, @column_name, @data_type, @max_length, @column_id

        END

        CLOSE tbl_cursor_changed
        DEALLOCATE tbl_cursor_changed

        FETCH NEXT FROM tbl_cursor
        INTO @schema, @table

    END

    CLOSE tbl_cursor
    DEALLOCATE tbl_cursor

    PRINT 'Collation For All Tables Done!'
    END

    EXEC  CHANGE_COLLATION

---Some collation does not changed due to contraint so i already changed by using Alter statement
checking more which collation of the column other then the default collation
--- MAKE A SCRIPT TO ALL THE TABLES COLUMNS WHICH SHOWS THE NOT IN 'SQL_Latin1_General_CP1_CS_AS' COLLATION

CREATE procedure test2 
as
DECLARE @collate SYSNAME
SELECT @collate = 'SQL_Latin1_General_CP1_CS_AS'

SELECT 
  '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
    ALTER COLUMN [' + c.name + '] ' +
    UPPER(t.name) + 
    CASE WHEN t.name NOT IN ('ntext', 'text') 
        THEN '(' + 
            CASE 
                WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1 
                    THEN CAST(c.max_length / 2 AS VARCHAR(10))
                WHEN t.name IN ('char', 'varchar') AND c.max_length != -1 
                    THEN CAST(c.max_length AS VARCHAR(10))
                WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1 
                    THEN 'MAX'
                ELSE CAST(c.max_length AS VARCHAR(10)) 
            END + ')' 
        ELSE '' 
    END + ' COLLATE ' + @collate + 
    CASE WHEN c.is_nullable = 1 
        THEN ' NULL'
        ELSE ' NOT NULL'
    END
FROM sys.columns c WITH(NOLOCK)
    JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
    JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
    AND c.collation_name != @collate
    AND o.[type] = 'U'

	exec test2

Manual collation change by Alter command

ALTER TABLE [dbo].[RESULTS]
DROP CONSTRAINT [CONSTRAINT_RESULTS]
GO
ALTER TABLE [dbo].[RESULTS]      ALTER COLUMN [Column_1] VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
GO
ALTER TABLE [dbo].[RESULTS] ADD  CONSTRAINT [CONSTRAINT_RESULTS] PRIMARY KEY CLUSTERED 
(
	[Column_1] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Hi there. Where did you get those queries? You didn't execute them, did you? There is at least one major bug in query #2. I wouldn't recommend using those.

But, more importantly, none of those scripts are the one that is causing the error (at least I don't think so). I need to see the query from the 3rd party application that is causing this error:

System.Exception: Cannot load config : Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CI_AI" in CASE operator occurring in SELECT statement column 5.

Also, for changing collations, you might want to take a look at the following post of mine that goes into detail about the undocumented "sqlservr.exe -q" option:

Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

1 Like

Hello again. Thanks for providing more info. Unfortunately, none of that is the info I am asking for. The spreadsheet itself and the config file shouldn't have anything to do with this error. You reported the error as starting with:

Cannot load config :

The loading of the config file (or data) is where the problem is. I need to see the query that is loading the config info. You might need to capture it with SQL Server Profiler or Extended Events.

1 Like

@Sameer Also is this for Microsoft SQL Server?

Yes, this is most definitely MS SQL Server. Based on the SQL_Latin1_General_CP1_CS_AS collation, the T-SQL being executed, the error message coming from System.Data.SqlClient, the screen shot of SQL Server Management Studio (SSMS), etc..

1 Like

@Sameer I know you said that the system was set to the correct collation, but just to be sure, please let us know the result from the following query:

SELECT db.[collation_name]
FROM   sys.databases db
WHERE  db.[name] = N'tempdb';

I am very thankful for your response the issue make me very worried and unable to sleep well as well (:no_mouth:
Please find below as asked the Sql Server Profiler unable to run i am trying to fix the .dll file

profiler
tempdb
master
model
msdb
Userdb
report server

Ok, so at least we know [tempdb] is the correct collation. That would have been the only possible source of an error outside of whatever the actual query is doing. At this point you either need to get SQL Server Profiler working (you might need to install the client tools, though I have never seen that error so you should do a search on it), OR you can use extended events.

The Sql Server Profiler is working please see the below script taken from Sql Server profiler and run on Query window.

select
    	tabs.name as TABLE_NAME,
    	cols.name as COLUMN_NAME,
    	cols.colorder as COLUMN_ORDER,
    	types.name as DATA_TYPE,
    	types.name +
    	case types.name
    		when 'char' then '(' + cast(cols.prec as varchar) + ') COLLATE ' + coalesce(cols.collation, types.collation)
    		when 'varchar' then '(' + cast(cols.prec as varchar) + ') COLLATE ' + coalesce(cols.collation, types.collation)
    		when 'nvarchar' then '(' + cast(cols.prec as varchar) + ') COLLATE ' + coalesce(cols.collation, types.collation)
    		when 'numeric' then '(' + cast(cols.xprec as varchar) + ', ' + cast(cols.xscale as varchar) + ')'
    		else ''
    	end + ' ' +
    	case cols.isnullable
    		when 1 then 'NULL '
    		when 0 then 'NOT NULL '
    	end
    	as TYPE_EXTENDED,
    	case
    		when keys.keyno is not null then 'True'
    		else 'False'
    	end as IS_PK,
    	keys.keyno as PK_ORDER,
    	case cols.isnullable
    		when 1 then 'True'
    		when 0 then 'False'
    	end AS IS_NULLABLE,
    	case types.name
    		when 'char' then cast(cols.prec as varchar)
    		when 'varchar' then cast(cols.prec as varchar)
    		when 'nvarchar' then cast(cols.prec as varchar)
    		when 'numeric' then cast(cols.xprec as varchar)
    		else NULL
    	end AS DATA_PRECISION,
    	case types.name
    		when 'numeric' then cast(cols.xscale as varchar)
    		else NULL
    	end AS DATA_SCALE,
    	CASE (cols.status & 128)
    		WHEN 128 THEN 'True'
    		ELSE 'False'
    	END AS IS_IDENTITY,
    	const_text.text AS DEFAULT_VALUE
    from syscolumns cols
    inner join sysobjects tabs
    	on tabs.id = cols.id
    inner join systypes types
    	on types.xtype = cols.xtype
    	and types.xusertype = cols.xusertype
    left outer join sysobjects pks
    	on pks.parent_obj = tabs.id
    	and OBJECTPROPERTY(pks.id, 'IsPrimaryKey') = 1
    left outer join sysindexes idx
    	on idx.id = tabs.id
    	and idx.name = pks.name
    left outer join sysindexkeys keys
    	on keys.id = tabs.id
    	and keys.colid = cols.colid
    	and keys.indid = idx.indid
    left outer join sysconstraints const
    	on const.id = tabs.id
    	and const.colid = cols.colid
    left outer join syscomments const_text
    	on const_text.id = const.constid
    where
    	OBJECTPROPERTY(tabs.id, 'IsUserTable') = 1
    	AND OBJECTPROPERTY(tabs.id, 'IsMSShipped') = 0 
     order by tabs.name, cols.colorder

@Sameer Thanks for capturing and posting that query. The problem is that the [collation] column of [syscolumns] has a collation of Latin1_General_CI_AI. It should not have this collation. It should have the same collation as the database default collation, but for some reason it's coming back as Latin1_General_CI_AI. This is not even being set in the syscolumns compatibility view, so I suspect it's a bug in the definition of the result set of that compatibility view. I get the same result on my system using SQL Server 2012, 2017, and 2019.

Unfortunately, this is not something that you can change or fix :crying_cat_face:

The real problem is this 3rd party software. What software is it? How old is it? This query is really bad:

  • It doesn't follow best-practices in several areas
  • It is clearly written for SQL Server 2000
  • It can't handle the CHAR datatype
  • It can't handle the VARBINARY datatype
  • It can't handle the MAX types ( VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) )
  • It cannot properly handle the TIME(n), DATETIME2(n), and DATETIMEOFFSET(n) datatypes.

You will have to find some other way to import the data. I think the Import Wizard can import from Excel.

1 Like

Hello Solomon.Rutzky Thank you !
Appreciate your help and reached to the root cause.

You are welcome, Sameer.

There is one minor detail from my previous comment that I need to clarify. Upon further investigation, it appears that the Latin1_General_CI_AI collation of the syscolumns.collation field (what is causing the error) is a bug that was introduced in SQL Server 2012. There have been two changes to syscolumns between SQL Server 2000 and 2012, though the first change didn't break your 3rd party app. Here is the basic progression of syscolumns between those versions:

  • As of SQL Server 2000 (maybe prior): systypes and syscolumns are system tables in each DB. No issue intermixing the collation columns from both tables as they have the same collation (which is the database's default collation as this is database-level meta-data).

  • Starting in SQL Server 2005: systypes and syscolumns become system compatibility views in each DB. The collation column in systypes comes from an undocumented built-in function. The query for the syscolumns view uses a UNION ALL, and both parts get their collation column from the same undocumented built-in function used in systypes. No issue intermixing the collation columns from both tables as they again have the same collation (they take on the database's default collation). Even with a different source for the value, the behavior ends up being the same as before. (Please note, though, that as of SQL Server 2005, dbo.systypes and dbo.syscolumns are deprecated and now sys.types and sys.columns should be used instead.)

  • Starting in SQL Server 2012: the definition of syscolumns changes slightly, and the second part of the UNION ALL query now selects from an internal system view. This changes the collation of the collation field. It now appears to be using the collation of the [mssqlsystemresource] hidden database (which is: Latin1_General_CI_AI).

    I would say that this is a bug because the behavior changed and is now inconsistent. HOWEVER, given that these are compatibility views, I would also think that fixing this is a very low priority for Microsoft, so don't expect it to happen. Still, I will file it as a bug as we shall see.

The only two possibilities for fixing this error are:

  1. Update the query, though if it's compiled into a 3rd party app, then you probably can't. And even if you could, that query still has other major issues.
  2. Change the database's default collation to Latin1_General_CI_AI. This should fix the error, but that query is still quite bad and mishandles several datatypes.

Take care, Solomon..

P.S. When requesting help in the future, please specify the version(s) of SQL Server that you are using. That extra info helps reduce the time it takes to debug things like this. Especially if you were using one version where something worked, but then upgraded to a newer version and that same thing stopped working.

The bug has been filed here:

Collation of [syscolumns].[collation] column is incorrect starting in SQL Server 2012