Collation problems

Question

Collation problems

agh100

Ten Centuries

Points: 1139
More actions
September 9, 2011 at 6:46 am

#247772

Hi,
I need help with this query...
I just restored a database to a new server. But the server has a different collation than the old server. Take a look at this query.. It ran on the old server.
WITH dest as (SELECT
UPC,
PROD_NAME,
PROD_SIZE,
UOM,
MANUFACTURER,
CATEGORY,
DESC_A,
DESC_B,
DESC_C,
DESC_E,
KEY_VAL,
HEIGHT,
WIDTH,
DEPTH,
UNITS_CASE,
UNITS_CASE_HIGH,
UNITS_CASE_WIDE,
UNITS_CASE_DEEP,
CASE_HEIGHT,
CASE_WIDTH,
CASE_DEPTH,
DISPLAY_HEIGHT,
DISPLAY_WIDTH,
DISPLAY_DEPTH
FROM dbo.ACN_PRODUCT)
UPDATE dest
SET dest.UPC = sourc.upc,
dest.PROD_NAME = sourc.name,
dest.PROD_SIZE = sourc.size,
dest.UOM = sourc.uom,
dest.MANUFACTURER = sourc.manufacturer,
dest.CATEGORY = sourc.category,
dest.DESC_A = sourc.desc_a,
dest.DESC_B = sourc.desc_b,
dest.DESC_C = sourc.desc_c,
dest.DESC_E = sourc.desc_e,
dest.KEY_VAL = sourc.key_val,
dest.HEIGHT = sourc.height,
dest.WIDTH = sourc.width,
dest.DEPTH = sourc.depth,
dest.UNITS_CASE = sourc.units_case,
dest.UNITS_CASE_HIGH = sourc.units_case_high,
dest.UNITS_CASE_WIDE = sourc.units_case_wide,
dest.UNITS_CASE_DEEP = sourc.units_case_deep,
dest.CASE_HEIGHT = sourc.case_height,
dest.CASE_WIDTH = sourc.case_width,
dest.CASE_DEPTH = sourc.case_depth,
dest.DISPLAY_HEIGHT = sourc.display_height,
dest.DISPLAY_WIDTH = sourc.display_width,
dest.DISPLAY_DEPTH = sourc.display_depth
FROM (SELECT
Hest.upc,
Hest.name,
Hest.size,
Hest.uom,
Hest.manufacturer,
Hest.category,
Hest.desc_a,
Hest.desc_b,
Hest.desc_c,
Hest.desc_e,
Hest.key_val,
Hest.height,
Hest.width,
Hest.depth,
Hest.units_case,
Hest.units_case_high,
Hest.units_case_wide,
Hest.units_case_deep,
Hest.case_height,
Hest.case_width,
Hest.case_depth,
Hest.Display_Height,
Hest.Display_Width,
Hest.Display_Depth
FROM OPENQUERY
(Test,'SELECT * FROM [''Product - Test$'']') as Hest
INNER JOIN[ICABSQLCLU04A\ICABSQLCLU04A].TestDB.dbo.[Stregkode] as SK on
Hest.UPC = SK.[Bar code No_]
Collate Danish_Norwegian_CS_AI
AND Hest.UPC IN (SELECT UPC FROM dbo.ACN_PRODUCT ) ) as sourc
WHERE dest.UPC = sourc.upc
I get the error : Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Danish_Norwegian_CI_AS" and "Danish_Norwegian_CS_AI" in the equal to operation.
Notice, that the problem is not the ICABSQLCLU04A server (this is latin something collation).. The problem is on the local server.. (The openquery is an linked server with ODBC to an Excel sheet). I believe that it might be because the tempdb has the default collation of the server (Danish_Norwegian_CS_AI), and the "destination database" is Danish_Norwegian_CI_AS..
I tried putting a Danish_Norwegian_CS_AI after the WHERE dest.UPC = sourc.upc ... Help please.

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

agh100 (9/9/2011)
I get the error : Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between ...

I would rebuild table setting columns to the proper collation or just alter the offending column/s - if you do any of these please do it in a copy of your target table for testing purposes.

Code below should change collation for a single column...

USE db_name

ALTER TABLE table_name

ALTER COLUMN column_name datatype COLLATE desired_collation NULL

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ken McKelvey SSCoach Points: 18859 More actions · Answer 2

This will probably work but will not use any indexes:

UPDATE P

SET UPC = sourc.upc,

PROD_NAME = sourc.name,

PROD_SIZE = sourc.size,

UOM = sourc.uom,

MANUFACTURER = sourc.manufacturer,

CATEGORY = sourc.category,

DESC_A = sourc.desc_a,

DESC_B = sourc.desc_b,

DESC_C = sourc.desc_c,

DESC_E = sourc.desc_e,

KEY_VAL = sourc.key_val,

HEIGHT = sourc.height,

WIDTH = sourc.width,

DEPTH = sourc.depth,

UNITS_CASE = sourc.units_case,

UNITS_CASE_HIGH = sourc.units_case_high,

UNITS_CASE_WIDE = sourc.units_case_wide,

UNITS_CASE_DEEP = sourc.units_case_deep,

CASE_HEIGHT = sourc.case_height,

CASE_WIDTH = sourc.case_width,

CASE_DEPTH = sourc.case_depth,

DISPLAY_HEIGHT = sourc.display_height,

DISPLAY_WIDTH = sourc.display_width,

DISPLAY_DEPTH = sourc.display_depth

FROM dbo.ACN_PRODUCT P

JOIN OPENQUERY (Test,'SELECT * FROM [''Product - Test$'']') AS sourc

ON P.upc = sourc.upc COLLATE DATABASE_DEFAULT

JOIN [ICABSQLCLU04A\ICABSQLCLU04A].TestDB.dbo.[Stregkode] AS SK

ON P.upc = SK.[Bar code No_] COLLATE DATABASE_DEFAULT;

Using temp tables may be better:

SELECT upc COLLATE DATABASE_DEFAULT AS upc

,name,size,uom,manufacturer,category,desc_a,desc_b,desc_c,desc_e,key_val,height,width,depth

,units_case,units_case_high,units_case_wide,units_case_deep,case_height,case_width,case_depth

,display_height,display_width,display_depth

INTO #test

FROM OPENQUERY (Test,'SELECT * FROM [''Product - Test$'']');

ALTER TABLE #test ADD PRIMARY KEY (upc);

SELECT upc

INTO #SK

FROM dbo.ACN_PRODUCT

WHERE 1=0;

ALTER TABLE #SK PRIMARY KEY (upc);

INSERT INTO #SK

SELECT [Bar code No_]

FROM [ICABSQLCLU04A\ICABSQLCLU04A].TestDB.dbo.[Stregkode];