Greetings,
I sent a detailed example of this to support and I’ll put it here in case it’s something simple.
I’m running into a lot of issue with dbamp and ordering by the account name. (both directly and also in the joins).
For example the 2 queries should produce the same results both return the same values. They do not because the order returned is different even when the values are the same.
select top(5) account.id from [salesforce].cdata.salesforce.account order by account.id COLLATE SQL_Latin1_General_CP1_CI_AS asc;
select top(5) cast(account.id as nchar(18)) from [salesforce].cdata.salesforce.account order by cast(account.id as nchar(18)) COLLATE SQL_Latin1_General_CP1_CI_AS asc;
If I change the query to do a case sensitive ordering like so, then the results match.
select top(5) account.id from [salesforce].cdata.salesforce.account order by account.id COLLATE SQL_Latin1_General_CP1_CS_AS asc;
select top(5) cast(account.id as nchar(18)) from [salesforce].cdata.salesforce.account order by cast(account.id as nchar(18)) COLLATE SQL_Latin1_General_CP1_CS_AS asc;
This also appears to be causing issues in left joins as internally SQL server wants to order the result set from the local table, order the result set from the Salesforce table and merge join the 2. The left join has rows not being found. Change to a inner join, which doesn’t do an order by according to the execution plan, and now the same rows have matching elements on Salesforce.
The question would be is this a known issue and why does it function this way? Also, this started after updating dbamp and there is been an uptick in API calls and we are not sure if that has been related.
