odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A collation conflict ... in the UNION ALL operator ... cannot be resolved. (#451)

I union all the distinct columns of the INFORMATION_SCHEMA.COLUMNS views of many databases which I query with a direct query:

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from xyz
UNION ALL 
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from bar
UNION ALL 
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from foo

My aim is to have a quick mapping between old and new columns and any other columns that have the same name, or to check for orphaned and duplicating columns. Having the distinct TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME of everything at hand and joining that over the column_name with itself, I get a full mapping of each column to any other entity you can find. Everything works fine until I also union all one table that is not a INFORMATION_SCHEMA.COLUMNS view but a mere table with its own collation on the column that has the unpivoted column_names in the rows of the field_name column. That is why I need to make the same columns by hand, as if it was an INFORMATION_SCHEMA.COLUMNS view:

SELECT DISTINCT 'abc' AS TABLE_CATALOG, 'dbo' AS TABLE_SCHEMA, 'xyz' AS TABLE_NAME, [xyz].field_name AS COLUMN_NAME
FROM xyz
ORDER BY [xyz].field_name;

Since the query puts together the output from many database catalogs, I cannot just code in TSQL in SSMS but need to do external queries from many databases and union all the output together. I take MS Access for this, but you could also reach that aim with a Console App in C# in Visual Studio. And since I have this one query (view "xyz") in there that I need to build up by hand, and since its field_name column has another collation than the COLUMN_NAME column of the INFORMATION_SCHEMA.COLUMNS view, the following error is thrown: German error message: > odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Ein > Sortierungskonflikt zwischen "SQL_Latin1_General_CP1_CI_AS" und > "Latin1_General_CI_AS" im UNION ALL-Operator, der in der 4-Spalte der > SELECT-Anweisung auftritt, kann nicht aufgelöst werden. (#451) Put in English with deepl: > odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A > collation conflict between "SQL_Latin1_General_CP1_CI_AS" and > "Latin1_General_CI_AS" in the UNION ALL operator occurring in the 4 > column of the SELECT statement cannot be resolved. (#451) I then tried it with the needed collation, see [How do I get something like the INFORMATION_SCHEMA.COLUMNS metadata view of the INFORMATION_SCHEMA.COLUMNS view itself?](https://serverfault.com/q/1155571/607547) , but with Latin1_General_CI_AS as my default collation of the database, and therefore with the right collation, this error is thrown: > The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. I tried to change the collations in other ways before and after this, and it seems as if MS Acces does not allow changing the collation in a SELECT command. How can I get rid of this error?