March 27, 2002 at 3:56 am
How can I change a Db Collection without using the Rebuildm.exe?
My Server has a CI collection and I have one DB with CS collection. I want to change this DB Collection to a CI, how can I do this?
March 27, 2002 at 5:48 am
First note this is for SQL 7 only and should not be used for 2000 as each DB have it's
own setting, also doing this on 7 will change the sort order and collation for every DB
on the server, that said.
This is not a method I have seen anywhere else and may not work (just a theory).
If you try please let me know the outcome as I have not had a chance to test.
Do not do this on a production system that already contains data unless you are willing to accept full
responsibility for what could potentially happen. After you have completed this stop and start the server
then run sp_helpsort to see if the new sort is in effect and test. Again this is a theory that should work
but I make not guarantee and you are on your own.
Try this, with SQL 7 look in BOL at Sort Order IDs to get the proper sort order id based on your unicode collation.
EXEC master.dbo.sp_configure 'default sortorder id', sortorderidnewvalue RECONFIGURE WITH OVERRIDE
GO
Look under "Unicode Collation" for the proper Unicode collation if you need to change.
--Only if you need.
EXEC master.dbo.sp_configure 'Unicode locale id', unicodecollationnewvalue RECONFIGURE WITH OVERRIDE
GO
And for this the defaul is usually 196609 which is normally left alone, But if this needs to be done then read the following from Microsoft.
Sorting of Unicode characters within a Unicode collation can be adjusted by modifying the Unicode comparison style.
Generally you should accept the default value of 196609. The Unicode comparison style is represented by the
CompStyle entry, a bitmap with the following components:
StyleValue (Hex)Value (Decimal)
Ignore case 0x00001 1
Ignore accent 0x00002 2
Ignore Kana 0x10000 65536
Ignore width 0x20000 131072
The CompStyle values can be calculated similarly to the NetworkLibs bitmask (discussed in the preceding Server-side
Network Libraries section). For example, the default of 196609 (0x30001 in hexadecimal) is the result of combining
the Ignore case, Ignore Kana, and Ignore width options:
1 (Ignore case)
+ 65536 (Ignore Kana)
+ 131072 (Ignore width)
---------
196609
--Only if you need.
EXEC master.dbo.sp_configure 'Unicode comparison style', newvalue RECONFIGURE WITH OVERRIDE
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply