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/

Code for Custom Background Colors

I am building a report that uses green, red, and white as the
background color of the cell. I've tried building a public shared
function with an if-elseif statement. My biggest problem is that I'm
not a VB programmer so I'm hacking away at this. I found a reference
to this at http://www.odetocode.com/Articles/130.aspx and tried to
follow the example but the example is based on older information.
Here is what my code looks like in the RDL:
<Code>Public Shared Function SetColor(ByVal Value As Integer) As
String
SetColor = "Red"
If Value < Fields!benchmark.value And Fields!hilo.value = "H"
SetColor = "Green"
ElseIf Value > Fields!benchmark.value And Fields!hilo.value = "Y"
SetColor = "Green"
ElseIf Value is nothing
SetColor = "White"
End If
End Function</Code>
When I try to compile the report I get this message:
There is an error on line 4 of custom code: [BC30469] Reference to a
non-shared member requires an object reference.
Does anyone know of a source for examples of how to write embedded
code in RS? I could really use the ability to do this but I'm just
too ignorant take advantage of this.On Dec 4, 10:47 am, Fred <fdk0154...@.cox.net> wrote:
> I am building a report that uses green, red, and white as the
> background color of the cell. I've tried building a public shared
> function with an if-elseif statement. My biggest problem is that I'm
> not a VB programmer so I'm hacking away at this. I found a reference
> to this athttp://www.odetocode.com/Articles/130.aspxand tried to
> follow the example but the example is based on older information.
> Here is what my code looks like in the RDL:
> <Code>Public Shared Function SetColor(ByVal Value As Integer) As
> String
> SetColor = "Red"
> If Value < Fields!benchmark.value And Fields!hilo.value = "H"
> SetColor = "Green"
> ElseIf Value > Fields!benchmark.value And Fields!hilo.value = "Y"
> SetColor = "Green"
> ElseIf Value is nothing
> SetColor = "White"
> End If
> End Function</Code>
> When I try to compile the report I get this message:
> There is an error on line 4 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference.
> Does anyone know of a source for examples of how to write embedded
> code in RS? I could really use the ability to do this but I'm just
> too ignorant take advantage of this.
You can't mix the Fields object in a Function, the value has to be
passed as a parameter to the function. Like any other language, the
Fields object would be out of scope when the back color expression is
inside the function. Also, you want to check if the Value object is
Nothing before you attempt to do math expressions against it (<, >).
I also assume you may want to use Double floating points in case you
have fractions and want them rounded correctly.
Function SetColor(ByVal Value As Double, ByVal BenchmarkValue As
Double, hilo As String) As String
If IsNothing( Value ) Then
SetColor = "White"
ElseIf Value < BenchmarkValue And hilo = "Y" Then
SetColor = "Green"
Elseif Value > BenchmarkValue And hilo = "N" Then
SetColor = "Green"
Else
SetColor = "Red"
End If
End Function
Then in the BackgroundColor Property, you would use an Expression like
= Code.SetColor( Fields!Value.Value, Fields!benchmark.Value, Fields!
hilo.Value )
-- Scott