I'll try and explain this in layman's terms, as these are the terms I understand.
This issue is not uncommon I believe. Collation's describe the ordering of data within columns. Mainly collation's are used to separate different languages alphabet ordering, as these differ between languages.
Specifically for your issue, Some columns on the database you are running the script on are using collation Latin1_General_CI_AS, whereas other columns are using SQL_Latin1_General_CP1_CI_AS.
- SQL_Latin1_General_CP1_CI_AS (legacy SQL Specific collation, which doesn't support unicode, and and will be removed from SQL Server one day)
- Latin1_General_CI_AS (windows collation which supports unicode)
Run this script to identify the columns on your database and their different collations:
FROM SYS.COLUMNS C
JOIN SYS.OBJECTS O
ON O.Object_id = C.Object_id
WHERE C.OBJECT_ID > 100
--AND C.Collation_Name <> 'Latin1_General_CI_AS'
AND O.Type = 'U'
I believe that because these collations aren't consistent, the error is occurring when running my script. Other scripts may also have a similar issue with the inconsistent collations.
You can overcome this by casting collations at every join, but i wont be updating my script to account for this as it will be time consuming. I'd advise that your column collations be consistent.