Collation

Hi,
what diff between SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CI_AS?
Why on win2003 install msde, the master database will have
SQL_Latin1_General_CI_AS instead of
SQL_Latin1_General_CP1_CI_AS?
Please advice. Thanks.> Why on win2003 install msde, the master database will have
> SQL_Latin1_General_CI_AS instead of
> SQL_Latin1_General_CP1_CI_AS?
SQL_Latin1_General_CI_AS is not a valid collation. Can you explain where
you are seeing this?
On 8.00.2039 I ran the following script:
SELECT 'foo' COLLATE SQL_Latin1_General_CI_AS
GO
--
Server: Msg 448, Level 16, State 1, Line 1
Invalid collation 'SQL_Latin1_General_CI_AS'.
To explain why databases have different *valid* collations, keep in mind
that you can create a database and specify a specific collation, otherwise
it will get the server default (which you set when you install SQL Server).
CREATE DATABASE foobar1 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
CREATE DATABASE foobar2 COLLATE SQL_Latin1_General_CI_AS
GO
--
The CREATE DATABASE process is allocating 0.63 MB on disk 'foobar1'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'foobar1_log'.
Server: Msg 448, Level 16, State 3, Line 2
Invalid collation 'SQL_Latin1_General_CI_AS'.
My suggestion is to use the server default when possible (which means
leaving the COLLATE keyword off of the CREATE DATABASE statement).|||Sorry, it is: Latin1_General_CI_AS on win2003,
but it will be SQL_Latin1_General_CP1_CI_AS on win2000. Why? I don't specify
any option during the installtion.
Please help.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ubLzwSIuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> SQL_Latin1_General_CI_AS is not a valid collation. Can you explain where
> you are seeing this?
> On 8.00.2039 I ran the following script:
> SELECT 'foo' COLLATE SQL_Latin1_General_CI_AS
> GO
> --
> Server: Msg 448, Level 16, State 1, Line 1
> Invalid collation 'SQL_Latin1_General_CI_AS'.
> To explain why databases have different *valid* collations, keep in mind
> that you can create a database and specify a specific collation, otherwise
> it will get the server default (which you set when you install SQL
> Server).
> CREATE DATABASE foobar1 COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> CREATE DATABASE foobar2 COLLATE SQL_Latin1_General_CI_AS
> GO
> --
> The CREATE DATABASE process is allocating 0.63 MB on disk 'foobar1'.
> The CREATE DATABASE process is allocating 0.49 MB on disk 'foobar1_log'.
> Server: Msg 448, Level 16, State 3, Line 2
> Invalid collation 'SQL_Latin1_General_CI_AS'.
> My suggestion is to use the server default when possible (which means
> leaving the COLLATE keyword off of the CREATE DATABASE statement).
>|||> but it will be SQL_Latin1_General_CP1_CI_AS on win2000. Why?
I'm not sure, I don't have any Win2000 servers around, only Windows 2003.
It may be the default collation when installing on Windows 2000, or it may
be the product of a SQL Server 7.0 upgrade.
The collations themselves are essentially the same, see the following:
SELECT name,description
FROM ::fn_helpcollations()
WHERE name IN
(
'Latin1_General_CI_AS',
'SQL_Latin1_General_CP1_CI_AS'
)
However, note that the SQL_Latin1 variation is used for backward
compatibility only. Going forward, SQL Server will be moving toward the
less verbose Latin1_ variations. See Go|URL|architec.chm::/8_ar_da_3xbn.htm
in Books Online for more information.

> Please help.
What exactly are trying to solve? If you are having collation conflicts
when merging/retrieving data across a linked server, you can set the
collations to be "compatible" using the following statement on each side:
EXEC master..sp_serveroption
@.server = 'Other_Linked_Server_Name',
@.optname = 'Collation Compatible',
@.optvalue = 'true'
You can use the same technique temporarily if you want to migrate the data
from the existing "badly collated" database to a new database with the right
collation.|||Thanks Aaron,
That's I'm looking for...
"Aaron Bertrand [SQL Server MVP]" wrote in message:
> What exactly are trying to solve? If you are having collation conflicts
> when merging/retrieving data across a linked server, you can set the
> collations to be "compatible" using the following statement on each side:
> EXEC master..sp_serveroption
> @.server = 'Other_Linked_Server_Name',
> @.optname = 'Collation Compatible',
> @.optvalue = 'true'
> You can use the same technique temporarily if you want to migrate the data
> from the existing "badly collated" database to a new database with the
> right collation.
>|||"Aaron Bertrand wrote:
> What exactly are trying to solve? If you are having collation conflicts
> when merging/retrieving data across a linked server, you can set the
> collations to be "compatible" using the following statement on each side:
> EXEC master..sp_serveroption
> @.server = 'Other_Linked_Server_Name',
> @.optname = 'Collation Compatible',
> @.optvalue = 'true'
>
Can I chanage the master, model, tempdb, msdb's collation without
rebuild(through option or property)? Thaks|||> Can I chanage the master, model, tempdb, msdb's collation without
> rebuild(through option or property)?
I don't think so.
If you can, I doubt it's supported.
I would feel much safer recommending detaching your database(s) and
reinstalling SQL Server. You're going to have to migrate the data to the
new collation anyway.|||Thanks Aaron.