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: 1000539 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)
Intro to Tally Tables and Functions

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]