Re: Problem with Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict



Bodo (1) writes:
I'm unable to modify my view due to an error:

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

Database colation is set to SQL_Latin1_General_CP1_CI_AS
...
The error refers to column [KALENDERMONAT_TEXT] in the view above.

In TB_INDUSTRIEKALENDER, the column with this name has the
collation SQL_Latin1_General_CP1_CI_AS. But in the view:


DDL of [dbo].[vw_AdressReport1]
--------------------------------------------------

SELECT col1,
KALENDERJAHR
,KALENDERMONAT
,KALENDERMONAT_TEXT
,MONATJAHR_TEXT
FROM [dbo].[TB_BETRIEB_FACTS] BF
Inner Join dbo.vw_KalenderJahrMonat KAL On
BF.[JAHRMONAT] = KAL.JAHRMONAT

KALENDERMONAT_TEXT comes from another table, where the collation
apparently is a different one.

You can find all columns that deviates from the database collation
with this query:

select o.name, c.name, c.collation_name
from sys.columns c
join sys.objects o on c.object_id = o.object_id
where c.collation_name <>
convert(nvarchar(100), databasepropertyex(db_name(), 'Collation'))
and o.schema_id <> 4
order by o.name, c.name



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.