Prev: DB1 filegroup backups used to refresh same filegroup on DB2 ?
Next: Cannot connect to Database Engine
From: Bodo on 15 May 2010 07:31
Hi,
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

Here is my ddl:
CREATE TABLE [dbo].[TB_INDUSTRIEKALENDER](

[KALENDERDATUM] [datetime] NOT NULL,

[KALENDERWOCHENTAG] [tinyint] NOT NULL,

[KALENDERWOCHE] [tinyint] NOT NULL,

[KALENDERJAHR] [int] NOT NULL,

[KALENDERMONAT] [tinyint] NULL,

[KALENDERTAG] [tinyint] NULL,

[QUARTAL] [tinyint] NULL,

[KALENDERWOCHENTAG_TEXT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[KALENDERMONAT_TEXT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ERSTELLTDATUMZEIT] [dbo].[T_SERVERDEFAULTDATUMZEIT] NULL CONSTRAINT
[DF__TB_INDUST__ERSTE__38996AB5] DEFAULT (getdate()),

[ERSTELLTBENUTZER] [dbo].[T_NAME] NULL CONSTRAINT [DF__TB_INDUST__ERSTE__092A4EB5] DEFAULT
(suser_sname()),

[GEAENDERTDATUMZEIT] [datetime] NULL,

[GEAENDERTBENUTZER] [dbo].[T_NAME] NULL,

[TIMESTAMP] [timestamp] NULL,

CONSTRAINT [PK_TB_INDUSTRIEKALENDER] PRIMARY KEY CLUSTERED

(

[KALENDERDATUM] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_TB_INDUSTRIEKALENDER] UNIQUE NONCLUSTERED

(

[KALENDERJAHR] ASC,

[KALENDERWOCHE] ASC,

[KALENDERWOCHENTAG] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]


--
The error occurs when I attempt to create this view:
create View [dbo].[vw_Report]

as

SELECT ,[JAHRMONAT]

,[KALENDERJAHR]

,[KALENDERMONAT]

,[KALENDERMONAT_TEXT]

FROM [dbo].[vw_AdressReport1]

UNION ALL

SELECT ,JAHRMONAT

,[KALENDERJAHR]

,[KALENDERMONAT]

,[KALENDERMONAT_TEXT]

FROM [dbo].[vw_AdressReport2]

The error refers to column [KALENDERMONAT_TEXT] in the view above.

----------------------------------------------------------

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

----------------------------------------

DDL of vw_KalenderJahrMonat

---------------------------------------

Create View [dbo].[vw_KalenderJahrMonat]

as

SELECT [KALENDERJAHR]

,[KALENDERMONAT]

,[KALENDERMONAT_TEXT]

,([KALENDERJAHR]*100) + [KALENDERMONAT] as JAHRMONAT

,Left(KALENDERMONAT_TEXT,3) + ' ' + convert(varchar,KALENDERJAHR) as MONATJAHR_TEXT

,Convert(datetime, convert(varchar,[KALENDERJAHR]) + Right('00'+convert(varchar,[KALENDERMONAT]),2)
+ '01') as MONATSERSTER

FROM [dbo].[TB_INDUSTRIEKALENDER]

Group by [KALENDERJAHR]

,[KALENDERMONAT]

,[KALENDERMONAT_TEXT]

--------------------------------------------------

DDL of [dbo].[vw_AdressReport2]

--------------------------------------------------

SELECT EREIGNISDATUM,

KALENDERJAHR

,KALENDERMONAT

,KALENDERMONAT_TEXT

,MONATJAHR_TEXT

FROM dbo.TB_FELDDATEN F

Inner Join dbo.TB_FAHRZEUGMARKE M On

Inner Join dbo.TB_INDUSTRIEKALENDER KAL On

KALENDERDATUM = EREIGNISDATUM





Anyone has any ideas on how to troubleshoot?



Thanks in advance

Bodo


From: Erland Sommarskog on 15 May 2010 12:15
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(a)sommarskog.se

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

From: Bodo on 17 May 2010 03:28
Hi Erland,
thanks for responding.

I run your query posted and as a result column KALENDERMONAT_TEXT collation
is Latin1_General_CI_AS whereas database and server collation is set to SQL_Latin1_General_CP1_CI_AS
I checked view definition that has no explicit collation option assigned and in the table columns
collation definition
is set to database default.

Appreciate any thoughts on how to troubleshoot.
Thanks
Bodo


"Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag
news:Xns9D79B9C0FE2E3Yazorman(a)127.0.0.1...
> 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(a)sommarskog.se
>
> 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
>


From: John Bell on 17 May 2010 04:15
Hi Bodo

Have you tried dropping and re-creating the view?

John

On Mon, 17 May 2010 09:28:11 +0200, "Bodo" <1> wrote:

>Hi Erland,
>thanks for responding.
>
>I run your query posted and as a result column KALENDERMONAT_TEXT collation
>is Latin1_General_CI_AS whereas database and server collation is set to SQL_Latin1_General_CP1_CI_AS
>I checked view definition that has no explicit collation option assigned and in the table columns
>collation definition
>is set to database default.
>
>Appreciate any thoughts on how to troubleshoot.
>Thanks
>Bodo
>
>
>"Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag
>news:Xns9D79B9C0FE2E3Yazorman(a)127.0.0.1...
>> 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(a)sommarskog.se
>>
>> 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
>>
>
From: Bodo on 17 May 2010 05:36
Thanks John,
yes that works very well.

However in the production database there are more tables
with inconsistent collation order.
So I have to modify these columns first and then rebuild
all depending views.

Again many thanks for jumping in.

Bodo

"John Bell" <jbellnewsposts(a)hotmail.com> schrieb im Newsbeitrag
news:sju1v5tcckvcn4vu980j6mkpi0q6h0eokf(a)4ax.com...
> Hi Bodo
>
> Have you tried dropping and re-creating the view?
>
> John
>
> On Mon, 17 May 2010 09:28:11 +0200, "Bodo" <1> wrote:
>
>>Hi Erland,
>>thanks for responding.
>>
>>I run your query posted and as a result column KALENDERMONAT_TEXT collation
>>is Latin1_General_CI_AS whereas database and server collation is set to
>>SQL_Latin1_General_CP1_CI_AS
>>I checked view definition that has no explicit collation option assigned and in the table columns
>>collation definition
>>is set to database default.
>>
>>Appreciate any thoughts on how to troubleshoot.
>>Thanks
>>Bodo
>>
>>
>>"Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag
>>news:Xns9D79B9C0FE2E3Yazorman(a)127.0.0.1...
>>> 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(a)sommarskog.se
>>>
>>> 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
>>>
>>


 |  Next  |  Last
Pages: 1 2 3
Prev: DB1 filegroup backups used to refresh same filegroup on DB2 ?
Next: Cannot connect to Database Engine