Collation URGENT!

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.
LVL 29
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
> Latin1_General_CS_AS.
this is case sensitive and the other one is case inseensitive

refer the following link
QPRAuthor Commented:
and Cp1?
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Aneesh RetnakaranDatabase AdministratorCommented:
run this to see the collations

select * from ::fn_helpcollations ()
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
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
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
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]+'] '+
        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 ( =
    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]

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?
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.
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.