October 15, 2025 at 7:12 pm
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
October 15, 2025 at 7:40 pm
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?
October 15, 2025 at 8:33 pm
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply