collation conflict

Question

Post reply

collation conflict

solus

Hall of Fame

Points: 3495
More actions
November 27, 2009 at 9:44 am

#218050

ive been testing some scripts on my local machine and now moved them to the development server. The scripts ran OK on my local server, but in development im getting a collation error
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
this is the script
CREATE TABLE #Prefix(
[Town] [varchar](40) NOT NULL,
[County] [varchar](50) NOT NULL,
[PostCodePrefix] [varchar](2) NOT NULL
) ON [PRIMARY]
GO
** insert some rows into #Prefix here
this bit of SQL causes the collation error
UPDATE tblAddress
SET County = mc.CountyName
FROM MasterCounty mc
INNER JOIN MasterTown mt ON mc.CountyID = mt.CountyID
INNER JOIN tblAddress ad ON ad.Town = mt.TownName
where ad.Town NOT IN (SELECT Town from #Prefix)
AND ad.Postcode is not null
GO
can anyone see why this is happening ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Seems like your table are set to different collation.

Check your table definition scripts to find the coulmn with a different collation.

To figure out which one it is you can either script out all related tables or you can set up a simple select statement (instead of update) and start to eliminate one table at a time from your join.

Or you can post the DDL (table scripts).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

support.sql Default port Points: 1444 More actions · Answer 2

You can use the COLLATE clause to force a collation.

Please read this article for more information.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx

SNM

Try Free Microsoft SQL Server Data Collector & Performance Monitor.

http://www.analyticsperformance.com[/url]

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

It sounds to me like you've gone through an upgrade process and TempDB now has a different collation that the database you're working with. It's a real PITA to change the default collation on TempDB... any chance of you changing the default collation on your other DB to match TempDB so you don't have to keep going through this? Remember, if you don't make a backup first, it's not my fault when something goes wrong. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 4

As an alternative you can define the collation of your temp table onlyy, not facing the pain Jeff mentioned.

That's basically the reason why I recommended to figure out the collation of your underlying tables to begin with...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]