July 2, 2021 at 10:34 am
Hi All
Working on a MS SQL Server 2017 and the server is in LATIN1_GENERAL_CI_AS, but the database I am working was migrated and has a database COLLATE value of SQL_LATIN1_GENERAL_CP1_CI_AS. I have a pre-developed PowerShell script that controls the creation of maintenance SP and those scripts are failing
'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation'
Any ideas what I can do to resolve this conflict?
Steve
July 2, 2021 at 2:00 pm
Well is the script trying to do joins between the two databases?
July 2, 2021 at 2:05 pm
No the T-SQL script runs and creates stored procedures based off a configuration table. Struggling to find the exact line of code that throws the error as it uses #temptables and dynamic sql. Using profiler and some bespoke debugging to find the line, but still working on it. My concern really around the mistmatch between the server and database, don't understand the impact?
July 2, 2021 at 2:48 pm
Well the #temp tables will be created with the database default collation so that will cause it to be out of sync. Is there a reason you can't change the new database to the server default? Those two collations should be pretty much the same.
Otherwise you would have to do something to force the #temp tables to be in the collation needed or explicitly state the collation in the queries, or use real tables in the database instead of #temp tables.
July 2, 2021 at 3:17 pm
You probably need to add "COLLATE" to JOINs and WHERE statements
SELECT A.FLD1, B.FLD6
FROM TABLE_A A
JOIN TABLE_B B on A.FLD1 COLLATE Latin1_General_CI_AS = B.FLD2
WHERE A.FLD3 COLLATE Latin1_General_CI_AS = B.FLD6
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply