Implicit conversion ... cannot be performed because the collation of the value is unresolved due to a collation conflict

Question

When I open a table in dbFront I get the following error:

Can't get data, Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "DanishNorwegianCIAS" and "SQLDanishPrefCP1CIAS" in add operator.

How do I resolve this error?

Answer 1

PROBLEM

dbFront concatenates fields for the table caption and also for lookup display values.

The issue is that dbFront is concatenating fields in your table that have different non-default collations set.

For example, your table might be set with a collation of "DanishNorwegianCIAS", but one or more of your table columns might have a non-default collation set to "SQLDanishPrefCP1CIAS".

SOLUTION

Since this is SQL Server, the best solution is to create a computed table column that concatenates the fields as you would like them while automatically dealing with the collation issues.

For example, you could create a computed column named [DisplayName] and set the computed column expression to:

(
  isnull([UserName], '')
  + ', ' + isnull([FirstName], '')
  + ', ' + isnull([LastName], '')
  COLLATE DATABASE_DEFAULT
)

The key piece to add is "COLLATE DATABASE_DEFAULT". This tells your database server to automatically use the default collation. Your specific collation needs may be different.

An additional benefit of computed columns, is that you can do much more then a simple concatenate.

Once you have created the computed column you can then update the table caption and the lookup columns so that they only use your new [DisplayName] column.