• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 788
  • Last Modified:

Collation URGENT!

Hi,
Looking for solutions to a problem I noticed that the server collation on box1 is SQL_Latin1_General_Cp1_CS_AS and on another it is Latin1_General_CS_AS. Looking deeper at our dev and production servers it seems that we have this mix in more places.
Is this a problem?

We are about to migrate some DBs to 2 new boxes and obviously want to get this right before doing so.
Looking back at 2 servers that have been operating as norm for ages and they are both SQL_Latin1_General_Cp1_CS_AS.
I don't even see Latin1_General_CS_AS listed in BOL.
Help!?
0
QPR
Asked:
QPR
  • 6
  • 6
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
QPR,
> Latin1_General_CS_AS.
this is case sensitive and the other one is case inseensitive

refer the following link
http://www.aspfaq.com/show.asp?id=2152
0
 
QPRAuthor Commented:
and Cp1?
0
 
QPRAuthor Commented:
Apologies I typed that incorrectly in my first post.
Our source DB is sql_latin1_General_CP1_CI_AS and the new one is latin1_General_CI_AS

The new is missing "SQL_" and CP1
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Aneesh RetnakaranDatabase AdministratorCommented:
run this to see the collations

select * from ::fn_helpcollations ()
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i think you have sql server 7.0 on one machine nd sql server 2000 in other machine
this will help you in fixing this
http://www.anotherurl.com/library/sql_collation.htm#fix
0
 
QPRAuthor Commented:
thanks, this throws up...

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

With the 2nd description being what we originally had.
So the question being what does that last bit mean (English please :) for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data and are their any ramifications of the new server not having it?

I have set the collation on the DB to be sql_latin1_General_CP1_CI_AS even though the server itself is set to latin1_General_CI_AS
0
 
QPRAuthor Commented:
Nope, no SQL 7.0 anywhere in the building.
The only difference OS/Version-wise is that the new SQL2k has been installed on Windows 2003 where as the original installation is on Windows 2000
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the following script will change all the columns to database default.Simply run the script in Query Analyzer , select all the records, and paste them into a new window to run them.




SELECT 'ALTER TABLE ['+USER_NAME(o.uid)+'].['+o.[name]+'] ALTER COLUMN ['+c.[name]+'] '+
    CASE
        WHEN c.prec IS NULL THEN t.[name]
        ELSE t.[name]+'('+CONVERT(varchar(5),c.prec)+')'
    END+' COLLATE '+t.collation
FROM syscolumns c
    JOIN sysobjects o ON (c.id = o.id)
    JOIN systypes t ON (c.xusertype = t.xusertype)
WHERE c.collation IS NOT NULL
    AND o.type = 'U'
    AND c.collation <> t.collation
ORDER BY o.[name]

0
 
QPRAuthor Commented:
Thanks but it is the 2 conflicting collation settings between the 2 servers that worry me.
What does "Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data" mean and what are the dangers of the new server not having this?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I think it is better you refer BOL for Collations and select the subsection 'Concepts'...
This can't be understand in a single line, better you read it first.
0
 
QPRAuthor Commented:
Thanks, I've read that and understand (sort of!) the concepts of collation and the different types.
What I still don't know is how (or if) the old default of sql_latin1_General_CP1_CI_AS and the new default latin1_General_CI_AS differ from each other. I have to work late tonight to migrate DBs from the old to the new server and I need to get this sorted before wasting hours building DBs that will have to be trashed and the server rebuilt due to incorrect collation settings.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
there's no difference between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. First is SQL Collation, second is Windows collation.
0
 
nmcdermaidCommented:
Here is a statement that I found in SQL Books Online

-----
In SQL Server 2000, you should primarily use Windows collations. You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server, or to maintain compatibility in applications developed using SQL collations in earlier versions of SQL Server.
-----


Therefore if you are starting anew and have no backwards compatability requirements, use a non SQL_* compatability.

i.e. use Latin1_*, not SQL_Latin1_*

The non-SQL collations are aligned with the OS collations.


aneeshattingal is correct in saying there is no difference between the collations. If you were to perform sorting and comparison operations on them in SQL you should come up with the same result.


In summary its really only a backwards compatability thing. The SQL_* set of collations match tha majority of past SQL collations. The non-SQL_* set of collations are a move towards sharing the same collations as the OS.


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now