Closed clintonbale closed 3 weeks ago
This is most likely due to different collations being used in the different environments. I'll look at improving the error reporting in the next update, but if this is the problem you can work around it by specifying the collation to use for the comparison, e.g.
select PROD_user.systemuserid, DEV_user.systemuserid
from [PROD CONNECTION].dbo.systemuser PROD_user
join [DEV CONNECTION].dbo.systemuser DEV_user ON DEV_user.domainname = PROD_user.domainname COLLATE Latin1_General_CI_AI
Looks like that was it, specifying the collation does allow the query to work.
Looking into the underlying cause: I'm not sure why these environments have different collations, they were created near the same time with the same settings, same region (Canada), same language (English United States).
DEV has 1033_CI_AI
while PROD has 4105_CI_AI
To check I used select SERVERPROPERTY('collation')
The following query to join the
systemuser
table between two environments fails:With the following error:
It does work however when joining on the
azureactivedirectoryobjectid
instead.