Collation errors...what is best way to deal with it?

Question

Collation errors...what is best way to deal with it?

water490

Ten Centuries

Points: 1210
More actions
October 15, 2025 at 7:12 pm

#4672557
Hi
I have a SP that occasionally get this error:
```
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
```
The query is close to 1000 lines long so I have no idea where this is without examining each line. Is there a way to set the collation in the SP so that it is used throughout the SP?
Thank you

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

water490 Ten Centuries Points: 1210 More actions · Answer 1

One thing I wanted to add. I have been experimenting with my query to see how it can be optimized. I have been trying the following approach:

Create temp table first

Create index

Add data

then I would use INSERT INTO temp_table

The other approach is

Add data first so I use select...into temp_table...

Add index on temp table

The latter approach doesn't give collation issue. It is the first one that is giving me problems b/c that is the only thing I changed. Prior to this change I never got a collation error. Even if I explicitly say "NAME nvarchar(10) Collate SQL_Latin1_General_CP1_CI_AS Not Null" in the table definition I still get collation errors later on in the query. I will try the other collation option from the error message when I am defining the table column as shown earlier and still giving me issues.

What is the best way to deal with this annoying collation issues in an efficient way?

Steve Jones - SSC Editor SSC Guru Points: 739813 More actions · Answer 2

You might have a different collation in tempdb. That's often a source of problems.

You can add the COLLATE clause to your query to force a certain collation.

https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver17

Johan Bijnens SSC Guru Points: 135593 More actions · Answer 3

On top of Steve's reply:

Make an inventory of all "non-default" collations in your instance / db and find the reason for that/those.

In many cases it is unintentional or someone scripted out stuff including collation and implemented it on your db without giving any thoughts.

Correct it whenever you can ( or you'll be stuck with dealing with collate or get bitten in the back frequently )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

water490 Ten Centuries Points: 1210 More actions · Answer 4

Thank you both.

Is there a query I can run to find the collation for each table everywhere in my DB? That way I can figure out where the problem is coming from.

Zidar SSC Enthusiast Points: 139 More actions · Answer 5

This query will tell you everything you need to know about all columns in all tables, including collations:

SELECT
C.TABLE_CATALOG AS SourceDB, C.TABLE_SCHEMA, C.TABLE_NAME,COLUMN_NAME
, ORDINAL_POSITION,COLUMN_DEFAULT, IS_NULLABLE
, DATA_TYPE , CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, DATETIME_PRECISION
, COLLATION_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] AS C
JOIN [INFORMATION_SCHEMA].[TABLES] AS T ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.Table_Type = 'Base Table'

Mind you, collations are on column level, decided at the time the column, or table, is created. You can change the collation of each column individually, as in this example:

ALTER TABLE dbo.MyTable
ALTER COLUMN CharCol VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8;

Good luck 🙂

This reply was modified 1 months ago by Steve Jones - SSC Editor.

Zidar's Theorem: The best code is no code at all...

Steve Jones - SSC Editor SSC Guru Points: 739813 More actions · Answer 6

Be careful changing collations and make sure you understand if this was done for a reason.