Collation URGENT!

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

and Cp1?

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

run this to see the collations

select * from ::fn_helpcollations ()

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

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

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

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]

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?

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.

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.

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.