Collation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Collation problem

Author

Topic

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 03:51:39

here's my querry :
"
select
CASE WHEN patindex('%Arad%', lm.denumire) <> 0 THEN 'Arad' WHEN patindex('%SM%', lm.denumire)
<> 0 THEN 'Satu Mare' WHEN patindex('%Adevarul%', lm.denumire) <> 0 THEN 'Arad' WHEN patindex('%Resita%', lm.denumire)
<> 0 THEN 'Resita' WHEN patindex('%Satu Mare%', lm.denumire) <> 0 THEN 'Satu Mare' WHEN patindex('%Deva%', lm.denumire)
<> 0 THEN 'Deva' WHEN patindex('%Tipo%', lm.denumire) <> 0 THEN 'Timisoara' WHEN patindex('%Timis%', lm.denumire)
<> 0 THEN 'Timisoara' WHEN patindex('%Cluj%', lm.denumire) <> 0 THEN 'Cluj' WHEN patindex('%CJ%', lm.denumire)
<> 0 THEN 'Cluj' ELSE 'Oradea' END AS city,
denumire,rtrim(ltrim(nume))+' ('+ltrim(rtrim(pfinal.marca))+')',seriecard,orai,oras,orelh,orenh,oresh from
personal.dbo.pfinal pfinal
left outer join personal.dbo.personal pers on pfinal.marca=pers.marca
left outer join personal.dbo.lm lm on pers.loc_de_munca=lm.cod "

And I get this error message :
"
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Romanian_CI_AS" in the equal to operation.
"

I mean to specify that all my db's are set to SQL_Latin1_General_CP1_CI_AS ... I can't seem to find where I have Romanian Collation .... Can it be that the data inserted in the those tables via an Integration Job are set for Romanian ? but if so ... the integration job wouldn't let any insertions if the collation ain't wright ?

Any ideas ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 03:59:06

look into each of table definition using sp_help tablename and check if any of columns have Romanian_CI_AS collation

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 04:08:47

quote:
Originally posted by visakh16

look into each of table definition using sp_help tablename and check if any of columns have Romanian_CI_AS collation

yes they have ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:10:25

then use collate for those columns to change to databse_default i.e in joins on conditions use :-
columnname COLLATE database_default
instead of just columnname

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 04:15:03

quote:
Originally posted by visakh16

then use collate for those columns to change to databse_default i.e in joins on conditions use :-
columnname COLLATE database_default
instead of just columnname

alter table pfinal alter column Marca
[Collate database_default]

Msg 2715, Level 16, State 6, Line 2
Column, parameter, or variable #9: Cannot find data type Collate database_default.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:17:39

nope not with alter table. just use in select itself like


select 
CASE WHEN patindex('%Arad%', lm.denumire) <> 0 THEN 'Arad' WHEN patindex('%SM%', lm.denumire) 
<> 0 THEN 'Satu Mare' WHEN patindex('%Adevarul%', lm.denumire) <> 0 THEN 'Arad' WHEN patindex('%Resita%', lm.denumire) 
<> 0 THEN 'Resita' WHEN patindex('%Satu Mare%', lm.denumire) <> 0 THEN 'Satu Mare' WHEN patindex('%Deva%', lm.denumire) 
<> 0 THEN 'Deva' WHEN patindex('%Tipo%', lm.denumire) <> 0 THEN 'Timisoara' WHEN patindex('%Timis%', lm.denumire) 
<> 0 THEN 'Timisoara' WHEN patindex('%Cluj%', lm.denumire) <> 0 THEN 'Cluj' WHEN patindex('%CJ%', lm.denumire) 
<> 0 THEN 'Cluj' ELSE 'Oradea' END AS city,
denumire,rtrim(ltrim(nume))+' ('+ltrim(rtrim(pfinal.marca))+')',seriecard,orai,oras,orelh,orenh,oresh from
personal.dbo.pfinal pfinal
left outer join personal.dbo.personal pers on pfinal.marca collate database_default=pers.marca collate database_default 
left outer join personal.dbo.lm lm on pers.loc_de_munca collate database_default=lm.cod collate database_default

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 04:20:32

quote:

Originally posted by visakh16

nope not with alter table. just use in select itself like


select 
CASE WHEN patindex('%Arad%', lm.denumire) <> 0 THEN 'Arad' WHEN patindex('%SM%', lm.denumire) 
<> 0 THEN 'Satu Mare' WHEN patindex('%Adevarul%', lm.denumire) <> 0 THEN 'Arad' WHEN patindex('%Resita%', lm.denumire) 
<> 0 THEN 'Resita' WHEN patindex('%Satu Mare%', lm.denumire) <> 0 THEN 'Satu Mare' WHEN patindex('%Deva%', lm.denumire) 
<> 0 THEN 'Deva' WHEN patindex('%Tipo%', lm.denumire) <> 0 THEN 'Timisoara' WHEN patindex('%Timis%', lm.denumire) 
<> 0 THEN 'Timisoara' WHEN patindex('%Cluj%', lm.denumire) <> 0 THEN 'Cluj' WHEN patindex('%CJ%', lm.denumire) 
<> 0 THEN 'Cluj' ELSE 'Oradea' END AS city,
denumire,rtrim(ltrim(nume))+' ('+ltrim(rtrim(pfinal.marca))+')',seriecard,orai,oras,orelh,orenh,oresh from
personal.dbo.pfinal pfinal
left outer join personal.dbo.personal pers on pfinal.marca collate databse_default=pers.marca collate databse_default 
left outer join personal.dbo.lm lm on pers.loc_de_munca collate databse_default=lm.cod collate databse_default

[/code]

Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
it's driving me crazy !!!

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 04:22:39

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:24:23

post your table definitions please along with collation, including only varchar fields will be enough

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 04:26:20

quote:
Originally posted by visakh16

post your table definitions please along with collation, including only varchar fields will be enough

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PFinal](
[Marca] [char](6) NULL,
[Seriecard] [char](10) NULL,
[OraI] [smalldatetime] NULL,
[Oras] [smalldatetime] NULL,
[Liber] [char](2) NULL,
[OrelH] [int] NULL,
[OrenH] [int] NULL,
[OresH] [int] NULL,
[Original] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:30:43

i told you to post collation details not entire table structure. post only character fields with their collations

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 05:47:21

quote:
Originally posted by visakh16

i told you to post collation details not entire table structure. post only character fields with their collations

i done it ... it should have been like this

alter table pfinal
alter column liber char(10)
Collate SQL_Latin1_General_CP1_CI_AS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 06:08:41

ok..i was suggesting you can do this in select itself rather than altering table

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-02-09 : 07:16:19