collation error

Hi
I am building a database using the Latin1_General_CI_AS collation.
Everything seems to work fine but I am stuck with this one view. When
creating the view I get this error:
Server: Msg 446, Level 16, State 9, Procedure qfmFieldUserNames, Line 4
Cannot resolve collation conflict for equal to operation.
View:
CREATE VIEW qfmFieldUserNames
AS
Select
T1.id,T1.name QFMTable,C1.colid,C1.name QFColumn,
Coalesce(D1.UserName,DL1.UserLabel,C1.name+'*') UserLabel
from sysobjects T1
left join syscolumns C1 on C1.id = T1.id
left join QFMDataItem D1 on D1.QFMTable = T1.name and D1.QFMColumn = C1.name
left join qfmQFMDataLabelLookup DL1 on DL1.QFMTable = T1.name and
DL1.QFMColumn = C1.name
where T1.type = 'U'
and C1.colid <> 1
and C1.name not in ('TimeStamp')
Does anyone have any tips?
Many Thanks.Lookup COLLATE in Books Online. Obviously the columns in the JOIN clause hav
e
different collations. You must declare which collation to use:
E.g.:
ColumnA has collation A
ColumnB has collation B
join ... on ColumnA collate B = ColumnB
or
join ... on ColumnA = ColumnB collate A
ML
http://milambda.blogspot.com/|||Thanks, I got to that too but all four columns are using the same collation
COLLATE SQL_Latin1_General_CP1_CI_AS
"ML" wrote:

> Lookup COLLATE in Books Online. Obviously the columns in the JOIN clause h
ave
> different collations. You must declare which collation to use:
> E.g.:
> ColumnA has collation A
> ColumnB has collation B
> join ... on ColumnA collate B = ColumnB
> or
> join ... on ColumnA = ColumnB collate A
>
> ML
> --
> http://milambda.blogspot.com/|||Weird. Try the database collation that you mentioned in your OP:
Latin1_General_CI_AS (on all affected columns in the FROM clause).
Could be due to the fact that you're trying to create a view.
ML
http://milambda.blogspot.com/|||ok I got pass the problem by collating the Coalesce part of the view as well
as these columns seems to have a different collation.
Select
T1.id,T1.name QFMTable,C1.colid,C1.name QFColumn,
Coalesce(D1.UserName,DL1.UserLabel,C1.name+'*') collate
SQL_Latin1_General_CP1_CI_AS UserLabel
from sysobjects T1
left join syscolumns C1 on C1.id = T1.id
left join QFMDataItem D1 on D1.QFMTable collate SQL_Latin1_General_CP1_CI_AS
= T1.name and D1.QFMColumn collate SQL_Latin1_General_CP1_CI_AS = C1.name
left join qfmQFMDataLabelLookup DL1 on DL1.QFMTable collate
SQL_Latin1_General_CP1_CI_AS = T1.name and DL1.QFMColumn collate
SQL_Latin1_General_CP1_CI_AS = C1.name
where T1.type = 'U'
and C1.colid <> 1
and C1.name not in ('TimeStamp')
"ML" wrote:

> Weird. Try the database collation that you mentioned in your OP:
> Latin1_General_CI_AS (on all affected columns in the FROM clause).
> Could be due to the fact that you're trying to create a view.
>
> ML
> --
> http://milambda.blogspot.com/|||Thank you, that's good to know.
ML
http://milambda.blogspot.com/