Matching Collation and Sort

I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
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 DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> 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
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
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
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> 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
>>
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> 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
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> 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
>>
>>
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> 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
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server
>>that produced the info below (by running sp_helpsort) is a SQL 2000 SP3
>>server as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> 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
>>
>>
>>
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>>I just installed a named instance and the output of sp_helpsort matches
>>your current production server and the collation I chose was
>>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>>The descriptive collation description during install was (under the SQL
>>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>>Character set"
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> 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
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code
>> Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get
>> the database collation. Have you moved a database from one server to
>> another with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot
>>seem to make the right choices to prevent collation conflicts in my
>>stored procedures. Can ANYONE please give me some insight on what
>>options to select in order to match this in SQL 2000 Service pack 3?
>>The server that produced the info below (by running sp_helpsort) is a
>>SQL 2000 SP3 server as well.
>> The closest I've been able to come at this point is to match
>> everything except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> 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
>>
>>
>>
>>
>