Collation Problem

Im getting different collation in different objects in a database...

• Collation of Tables is 'SQL_SwedishStd_Pref_CP1_CI_AS'
• Collation of Stored Procedure is 'S开发者_C百科QL_Latin1_General_CP1_CI_AS'

Which is creating problem while executing query. Can someone help me out?

Thanks In Advance

You will have to specify according to which collation you wish to do the join (or where clause)

See the example below on how to code this.

create table x( id int, name varchar(256) collate SQL_SwedishStd_Pref_CP1_CI_AS)
create table y( id int, name varchar(256) collate SQL_Latin1_General_CP1_CI_AS)

insert into x values (1, 'Filip')
insert into y values (1, 'Filip')

So if you wish to join based on Latin1 collation you write the query like this:

select * 
  from x
  join y 
    on x.name collate SQL_Latin1_General_CP1_CI_AS = y.name 

If you wish to join based on Swedish collation you write the query like this:

select * 
  from x
  join y 
    on x.name = y.name collate SQL_SwedishStd_Pref_CP1_CI_AS 

If the collation difference has been created by incorrectly defining the table you can alter the column to switch the collation:

alter table x alter column name varchar(256) collate SQL_Latin1_General_CP1_CI_AS

If you then run the query like this, no more errors will occur:

select * 
  from x
  join y 
    on x.name = y.name