Collation Settings in SQL2008 - SQL Server Forums

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

SQL Server 2008 Forums

SQL Server Administration (2008)

Collation Settings in SQL2008

Author

Topic

jmill130
Starting Member

26 Posts

Posted - 2009-05-14 : 11:56:33

I am getting an odd error which i dont fully understand.

I have 3 servers:

#1 - Running SQL 2000 - 8.0.2050 with following collation: SQL_Latin1_General_CP1_CS_AS

#2 - Running SQL 2000 - 8.0.2039 with following collation:
SQL_Latin1_General_CP1_CI_AS

#3 - Running SQL 2008 - 10.0.2531 with following collation:
SQL_Latin1_General_CP1_CI_AS

I have tons of queries that update tables on #2 using #1. I now want to run these queries from #3 and i get the following error:

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

MY SQL Code:

BayCurrent_temp is on both #2 and #3 servers (this sql code runs fine from #2, not on #3)

Update BayCurrent_temp
set bayCurrent_temp.AttPhys = MyAttPhys.AttPhys
From BayCurrent_temp inner Join baymtdr1.DR_VIEWS.rkegley1.InptAttending As MyAttPhys
on BayCurrent_temp.VisitID = MyAttPhys.VisitID
and SourceID = 'JHB'

I am not sure why this works fine from a 2000 server but not 2008 when the collate settings appear to be the same between #2 and #3.

#1 and #2 collate settings are different but the query still runs with no problems, does 2008 have some special check. Are my queries not being structured the same? What could be happening?

I know I can add collate code to the queries, but we are talking 100s of queries and why do they work from #2??

Any help is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 14:21:29

are you sure you dont use a collation other than that used by database for table BayCurrent_temp in #2 & #3?

jmill130
Starting Member

26 Posts

Posted - 2009-05-14 : 14:51:18

ill double check, i did and entire backup and restore from #2 to #3. I would think it would stay the same..Ill confirm.

jmill130
Starting Member

26 Posts

Posted - 2009-05-20 : 11:50:15

AS followup.. I ended up adding COLLATE SQL_Latin1_General_CP1_CI_AS to all of my join statements that compare text fields... I guess thats the best solution i could get to work..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources