I am trying to compare data across two databases. I have added the following line to the code which previously ran fine:

filter.Add(New FieldCompareSetPredicate(AccountFields.CRef, AccountsDB.HelperClasses.CustomerMatrixFields.CCode, SetOperator.In, (AccountsDB.HelperClasses.CustomerMatrixFields.ChargeCode = "HB" And AccountsDB.HelperClasses.CustomerMatrixFields.ServiceChargeGbp = 0), True))

(Looking for Accounts whose C_Ref is not in the CustomerMatrix as filtered.)

The typed list is populated using the line of code:

ResList.Fill(0, sorter, True, filter)

I get the error: ORMQueryExecutionException was unhandled An exception was caught during the execution of a retrieval query: Cannot resolve collation conflict for equal to operation. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

The following SQL query would trigger the same error:

SELECT   *
FROM         [DEEHURST\rpb].Account
WHERE    (C_Ref IN
                          (SELECT    C_Code
                            FROM          AccountsDB.dbo.CustomerMatrix
                            WHERE     (ChargeCode = 'HB') AND (ServiceCharge_PC = 0)))

Whereas this would run fine

SELECT   *
FROM         [DEEHURST\rpb].Account
WHERE    (C_Ref COLLATE database_default IN
                          (SELECT    C_Code
                            FROM          AccountsDB.dbo.CustomerMatrix
                            WHERE     (ChargeCode = 'HB') AND (ServiceCharge_PC = 0)))

How can I get the COLLATE database_default in there or otherwise get round this problem?

I am running LLBLGen Pro 2.6 on VB.NET

I have added the following line to the code which previously ran fine

When this started to crash? Did you do some change on some DB? Collation?

If it's totally necessary, you could do something like:

filter.Add( New FieldCompareSetPredicate(
     AccountFields.CRef.SetExpression(
                new DbFunctionCall("{0} COLLATE database_dafault", new object[]{  
                    AccountFields.CRef })),
     AccountsDB.HelperClasses.CustomerMatrixFields.CCode, 
     SetOperator.In, 
     (AccountsDB.HelperClasses.CustomerMatrixFields.ChargeCode = "HB" 
          And AccountsDB.HelperClasses.CustomerMatrixFields.ServiceChargeGbp = 0), 
     True))

Make your DBs match would be better though.

I had to use normal brackets, not square brackets and put a space after database_default but that's done the trick. Thanks a lot.