Collation issues in multiple tables.

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

Database Design and Application Architecture

Collation issues in multiple tables.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-07-27 : 20:16:32

Hi.
I think i've sorted it out but i need a confirmation.
I tried to join database tables and got "Cannot resolve the collation conflict between SQL_LAtin , etc etc".
What i've done was to use the collate word and DATABASE_DEFAULT as the collation.I works now but i've tested a little and i saw the i can skip the error if i have one collate for a database to the select,from and where.So basically i only specified p.e. 1 collate on the select statement,even though i had 3-4 selections from the external database.
So will this have any implications or i'm ok with this?
Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-28 : 02:03:08

when you are joining tables use this:


select *
from database1.dbo.table1
join database2.dbo.table2
on table1.id = table2.id collate Latin1_General_CS_AS


select *
from database1.dbo.table1
where column1 collate Latin1_General_CS_AS = 'some_string'

Kristen
Test

22859 Posts

Posted - 2010-07-28 : 02:22:17

Only need to add the COLLATE statement when comparing string columns (of potentially different collations - e.g. between databases) - so not needed on Numeric / Date column comparisons

I've never needed them when comparing to a 'String Constant' - but I may not have been using collations that needed it

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-07-28 : 12:08:43

Thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources