Cross Environment Join: Null Reference Exception

[clintonbale]

The following query to join the systemuser table between two environments fails:

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

With the following error:

Msg 10337, Level 16, State 1, Line 1
Object reference not set to an instance of an object.

See the Execution Plan tab for details of where this error occurred

It does work however when joining on the azureactivedirectoryobjectid instead.

---

• Deployment: Online
• DB Version: 9.2.24071.168
• Connection Controls Version: 1.2023.6.56
• XrmToolBox Version: 1.2023.12.68
• Tool Version: 9.2.0.0

[MarkMpn]

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

[clintonbale]

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')