0

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

enter image description here

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:

enter image description here

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?, but with Latin1_General_CI_AS as my default collation of the database, and therefore with the right collation, this error is thrown:

enter image description here

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?

1 Answer 1

0

The error is thrown by column 4 (field_name = COLUMN_NAME) of the view that I get from the data table (and not like the others, from an INFORMATION_SCHEMA.COLUMNS view). You cannot change the collation within MS Access queries, it seems, it will always show you the error that is already shown in the question.

To get rid of the error, I found two workarounds. You either need to change it already on the server, by making a view or table with the right collation and taking that as the source, or you take one of these workarounds:

  1. You can make a full table import instead of a direct query.

    enter image description here

    Then, the error will be gone even if the collation is not the same, you do not need to change anything, just replace the direct query view name with the imported table name, it is enough to change the suffixes to change the union query that is built on top.

  2. You can change the order of the unioned views a bit, just try out a few changes. For me, it was enough to put the xyz view that throw errors at the 1st or 2nd place instead at the end of the list or 3rd place. Example with just the three views.

    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
    

    would then become:

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

    That is the better workaround since it does not need to download the whole table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.