SQL Server - 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

Asked By Bodo on 15-May-10 07:31 AM
Hi,
I am 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]




Bodo replied on 17-May-10 03:28 AM
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
John Bell replied to Bodo on 17-May-10 04:15 AM
Hi Bodo

Have you tried dropping and re-creating the view?

John
Bodo replied to John Bell on 17-May-10 05:36 AM
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 replied to Bodo on 17-May-10 06:10 AM
Hi Bodo

Forcing the collation in the view may mean that you can "get away"
with not changing the column collation, but that is really a kludge.
Overall keeping collations consistent for the instance and database
helps to avoid this type of issue. Having the same collatons in dev,
test and live is also very important.

It looks like you need to enforce standards and consistency, you may
want to review your version control, build and release processes.

John
Bodo replied on 17-May-10 01:49 PM
Thanks Erland for your post and sample query on how to determine inconsistency
in collation order.
This was very valueable to find out the table columns affected.

Unfortunately SQL Server Management Studio "supports" you with collation by
adding the db default collation with each Create table statement that you can produce
by right klick on a table ... script table  to...
e.g:
CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](

[SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,

[KALENDERJAHR] [int] NOT NULL,

[KALENDERMONAT] [tinyint] NOT NULL,

...

So there has been a few scripts in the past with explicit Collate specification that I give to my
client.
Later on I always removed that clause from each create statement.
Now the production database defintion is consistent to my development system.

Again many thanks to both of you!

Best regards
Bodo
Sylvain Lafontaine replied to Bodo on 17-May-10 02:03 PM
You can deactivate the option of scripting the collations: Tools | Options |
SQL Server Object Explorer | Scripting | Include collation -> set to False.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
Bodo replied to Sylvain Lafontaine on 17-May-10 02:20 PM
Thanks Sylvain,
I thought there must be such option in SSMS, however I cannot find this option in Tools-Options-SQL
Server Object Explorer.
I work with MS SSMS 9.0 (2005).

Thanks
Bodo
Sylvain Lafontaine replied to Bodo on 17-May-10 04:39 PM
I do not know for sure for SSMS 2005 but for SSMS 2008, make sure that you
are looking under the Scripting node instead of the Commands node when
expanding the SQL Server Object Explorer properties.

it is the third option under the sub-node "Table and view options" (still for
SSMS 2008).

Also, you can install and use SSMS 2008 for working against SQL-Server 2005
databases.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
Bodo replied to Bodo on 17-May-10 05:46 PM
Thanks a million to all of you
- I am on my way to SQL 2005 SP3

Bodo