Menu

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

0 votes

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?

in Version 1.2 by (7.0k points)

1 Answer

0 votes
 
Best answer

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.

by (64.3k points)
selected by
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
 |
Powered by Question2Answer
...