collation conflict - one DB one Server 1

[SQLScholar]

Hey all.

I am getting a collation conflict - but every thread i look at it seems to be caused by have 2 DB`s or 2 servers running different collation. Difference is - i am only querying one DB on one server. Here is my code:

select
 [policy_num], [Optional Life],
 [Tax Rate],
 Papolicy.[Level Of Cover],
 Papolicy.[Range of Cover],
 Papolicy.[Monthly_Amount],
 Papolicy.Cover_Type,
 AnnualNetPrem,
 cast(ISNULL(Monthly_Amount,0) as float) - (CAST (ISNULL(AnnualNetPrem,0) as float)) as [derived monthly PA prem],
 [last transaction type],
 VwFFPAPrems.cost,
 VwFFLifePrems.cost
 
from
 Papolicy
 
 inner join
 VwFFPAPrems
 on
 VwFFPAPrems.[range of cover] = papolicy.[range of cover]
 and
 VwFFPAPrems.[level of cover] = papolicy.[level of cover]
 and
 VwFFPAPrems.[cover_type] = papolicy.[cover_type]
 

 inner join
 VwFFLifePrems
 on
 VwFFLifePrems.[cover_type] = papolicy.[cover_type]
  and
  VwFFLifePrems.[level of cover] = papolicy.[level of cover]
 

WHERE
 Papolicy.policy_type= 'FF'
AND
 Papolicy.[Life cover]= 'yes'
and
Papolicy.[level of cover] = 7
and
Papolicy.[range of cover] = 'STANDARD'
and
Papolicy.[tax rate] = '0.05'
and
Papolicy.[cover_type] = 'Single'
order by [monthly_amount]

Any ideas what this could be?

Many thanks

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[MissyEd]

Off the top of my head - which is probably not a good idea before coffee, check the collation for each of your varchar/text columns used within your query and ensure they match.

My lazy way to do it is to script each table in Enterprise Manager (Right-click on the table, All Tasks, Generate SQL Script) and just eyeball the collation shown.

It's likely that you have created a column with a different collation.

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[SQLScholar]

AHA - i think i might know the issue, unfortunately i dont know the solution.

The tables which i create the two views from, dont have any varchars. But when i create the views i have created them with some extra fields, which i assume are being created as varchars. So how do i set the collation for these?

Many thanks

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

AHA - got it

You define the collation after you define the column but before an alais.

So

'THIS ONE' COLLATE Latin1_General_CI_AS as [fieldname]

Thanks for putting me on the right track! STAR

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[MissyEd]

Hey,

Great to hear you sorted it out

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter