Help with Collation - Decoding the Compatibility Designator

We have a 3rd-party written application that was installed and has
been upgraded on the same server. We want to move the database to a
SAN, but it has a compatibility collation. The select
serverproperty(N'Collation') command returns:
Compatibility_52_409_20001. I have been reading about decoding this
compatibility designator, but I'm stuck. I know the 52 refers to
Dictionary Order, case-insensitive, for use with 1252 character set.
The 409 is hex 0x409 or 1033, which translates to General Unicode.
From what I've read, the 20001 is also hex which means it is 0x20001
or 131073.
Anyone have any idea what the 131073 translation is? I'd like to know
what collation sequence to choose when installing SQL 2000 to match up
with this.
Any help greatly appreciated.
Thanks,
BillThere's some more info on this in 270042 INF: Description of SQL Server
Compatibility Collations (http://support.microsoft.com/?id=270042).
Here are the bits that make up the last portion of a compatibility
collation name:
+===============+=============+==================+
| Style | Value (Hex) | Value (Decimal) |
+===============+=============+==================+
| Ignore case | 0x00001 | 1 |
+===============+=============+==================+
| Ignore accent| 0x00002 | 2 |
+===============+=============+==================+
| Ignore Kana | 0x10000 | 65536 |
+===============+=============+==================+
| Ignore width | 0x20000 | 131072 |
+===============+=============+==================+
131073 is 131072 (ignore width) + 1 (ignore case). So this collation is
accent-sensitive, kana-sensitive, case-insensitive, width-insensitive.
> I'd like to know what collation sequence to choose
> when installing SQL 2000 to match up with this.
You cannot select a compatibility collation during a clean interactive SQL
2000 setup; you can only choose one of the so-called "named collations".
Your options here are:
- Install a SQL 7.0 instance with the same collation properties and do an
in place upgrade of this instance to SQL 2000. An in-place instance
upgrade always retains the existing collation settings, even if they are
non-standard.
- Use DTS to move the data in this database to a db with a more standard
collation that you can select as an instance-level collation when
installing SQL 2K.
- Modify stored procs that join temp tables to user tables to add a
"COLLATE database_default" to the column definition of temp table
char/varchar columns. In most apps there just a handful of these
modifications are required, and one benefit of this approach is that the
app will be "system collation agnostic" from that point forward and can be
successfully run in a mixed collation evironment or on any other server.
- Do a silent install of SQL 2000. Unlike an interactive install, for a
silent install you can specify any collation name in the .ISS file, even if
it is a compatibility collation.
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: bengels@.wi.rr.com (Bill Engels)
Newsgroups: microsoft.public.sqlserver.server
Subject: Help with Collation - Decoding the Compatibility Designator
Date: 31 Oct 2003 11:32:42 -0800
Organization: http://groups.google.com
Lines: 19
Message-ID: <7b42351f.0310311132.4c922d38@.posting.google.com>
NNTP-Posting-Host: 204.87.60.232
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1067628762 19180 127.0.0.1 (31 Oct 2003
19:32:42 GMT)
X-Complaints-To: groups-abuse@.google.com
NNTP-Posting-Date: Fri, 31 Oct 2003 19:32:42 +0000 (UTC)
Path:
cpmsftngxa06.phx.gbl!cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!po
stnews1.google.com!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:314087
X-Tomcat-NG: microsoft.public.sqlserver.server
We have a 3rd-party written application that was installed and has
been upgraded on the same server. We want to move the database to a
SAN, but it has a compatibility collation. The select
serverproperty(N'Collation') command returns:
Compatibility_52_409_20001. I have been reading about decoding this
compatibility designator, but I'm stuck. I know the 52 refers to
Dictionary Order, case-insensitive, for use with 1252 character set.
The 409 is hex 0x409 or 1033, which translates to General Unicode.
From what I've read, the 20001 is also hex which means it is 0x20001
or 131073.
Anyone have any idea what the 131073 translation is? I'd like to know
what collation sequence to choose when installing SQL 2000 to match up
with this.
Any help greatly appreciated.
Thanks,
Bill|||Thanks for the help Bart. Appreciate the answer and ALL your help in the newsgroups.
Bill