Collation problem SERVER 7.0 AND 2005

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 problem SERVER 7.0 AND 2005

Author

Topic

peko
Starting Member

3 Posts

Posted - 2008-02-11 : 08:57:10

we have a problem with the collation of databases between sql server 7.0 and SQL SERVER 2005. The message that pops up is next:

"Mensagge. 468, level 16, state 9, Line 1
Problem of collation can not be solved between "Compatibility_52_c0a_30001" and "SQL_Latin1_General_CP1_CI_AS of UNION command.

Te script is lanch from sqlserver 2005, and it doesn´t work with UNION command but it works if you lanch them separately.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-11 : 23:16:23

You can specify collation in select statement.

peko
Starting Member

3 Posts

Posted - 2008-02-12 : 07:17:13

1. CREATE VIEW dbo.SSP_EMD_PERSON AS
(SELECT STD_ID_PERSON STD_ID_PERSON FROM dbo.STD_PERSON)

2. CREATE VIEW dbo.SSP_EMD_PERSON_____ AS
(SELECT E.STD_ID_PERSON STD_ID_PERSON FROM dbo.M4_S_EMPLEADOS E)

3. SELECT * FROM SSP_EMD_PERSON
UNION
SELECT * FROM SSP_EMD_PERSON_____
go

When executing this script of step 3 return message error on top.
Thanks Rmiao.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-12 : 23:50:29

You can try something like:

SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as;

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-12 : 23:56:13

You will have to specify COLLATE clause for each of the character columns like this:

Select 
 col1 collate SQL_Latin1_General_CP1_CI_AS,
 col2 collate SQL_Latin1_General_CP1_CI_AS,
 ...
from Table1
union
Select
 col1 collate SQL_Latin1_General_CP1_CI_AS 
 ...
from Table2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

peko
Starting Member

3 Posts

Posted - 2008-02-13 : 10:51:54

Thank you Harsh but we need to know which the equivalent of UNICODE SORT "Compatibility_52_c0a_30001" for SQL SERVER 2005 in other to avoid changing all the tables of the database.

when we execute sp_hrlpsort "Compatibility_52_c0a_30001" in my sqlserver7 we get this codes (we would want to get the same result in SQLSERVER 2005 for the linked servers):

Unicode data sorting
----------------------
Locale ID = 3082
case insensitive, kana type insensitive, width insensitive
Sort Order Description --------------------------------------------------------------
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Characters, in Order --------------------------------------------------------------------
! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }
~ ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾
¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å Æ=æ B=b C
=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í Î=î Ï=ï J
=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø P=p Q=q R=r S
=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z Ð=ð Þ=þ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources