Collation error when running a import job after migrating database

Question

Firstly, I'm not very experienced with SQL so apologies in advance for any incorrect terminology.

We have a bespoke application which reports data to SQL express at 5 different sites. These databases are linked to a SQL 2012 server and a job runs for each linked database and imports data into the main 2012 database. Has worked well so far, but I have been tasked with migrating to a new SQL 2019 server. Everything seems to have gone well apart from the import job. it's failing because of the following error.

Executed as user: #####\Administrator. Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed

Comparing the connection properties for the user on the old and new, and the old has a collation of Latin1_General_CI_AS, whereas the new server, the user has a collation of SQL_Latin1_General_CP1_CI_AS. So I suspect this is the issue.

I would really appreciate some guidance on how (if possible) I can change the collation for the user and if this can't be done, is there anything else I can try.

Many thanks in advance.

Graham

Answer 1

Hi @Graham Thackery

Regarding changing the collation for the entire database, see this doc: Set or change the database collation.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I can change the collation for the user

Collation is not related to a user, but to database design & server settings.

The effective collation for data is defined on table => column level.

To be true: Better hire a consultant to assist you (or doing the complete job), even if it is expensive.