would this script use a temporary table? Then the table will have the server default collation.
If it just a quick one-time solution, then I would modify the source query to provide the data in the correct collation, here Hebrew_CI_AS
The problem should be with the string literal (i.e. the ''SERVERA'') as that takes on the Collation of the database where that statement is executing.
So try the following for the top part of your query:
SELECT *
FROM OPENQUERY([SERVERA],
'SELECT ''SERVERA'' COLLATE Latin1_General_CI_AS, [PhoneNum]
...
And, the bottom part of the UNION ALL (the part for [SERVERB]) should probably select "SERVERB" instead of "SERVERA" as the string literal :).
And to be technical, it is taking on the Collation of the DB, not the server, though if the default connection is to master (or any system DB), then that would naturally be the same as the Server-level Collation since the Server-level Collation is used to create the system DBs.
UPDATE:
The new error you are getting after adding the COLLATE clause to the 2nd query in the UNION ALL is due to adding the COLLATE clause to too many of the fields. It needs to go only to the literal string, and not on the resourcephone.[PhoneExt] field. Doing that one extra step put the fields that were already in agreement into conflict. Since both of those table fields are in Latin1_General_BIN, there is no need to override the Collation on the resourcephone.[PhoneExt] field from either Server. It is only the string literal "field" that needs to be overriden, and only from one of the two servers: you just need to make one match the other.
Adding the COLLATE clause to all fields, especially ones that are already working as expected (i.e. the resourcephone.[PhoneExt] field) is unnecessary and is over-complicating the query.
Best Answer
SQL Server 2017 documentation:
Always Encrypted is not supported for the columns with the below characteristics:
(...)
String (varchar, char, etc.) columns with non-bin2 collations
(...)
Source: Always Encrypted
Looks like it is changing in SQL Server 2019:
Since its initial release, Always Encrypted has had a restriction regarding the use of collations: non-BIN2 collations are not allowed for character string columns encrypted using deterministic encryption. This restriction also applies to enclave-enabled string columns.
The use of non-BIN2 collations is permitted for character string columns encrypted with randomized encryption and enclave-enabled column encryption keys. However, the only new functionality that is enabled for such columns is in-place encryption. To enable rich computations (pattern matching, comparison operations), you must ensure the column uses a BIN2 collation.
Configure Always Encrypted with secure enclaves