March 25, 2002 at 12:52 pm
When I tried to restore a database file to a new server. I got error msg like: the database you are attempting to restore was backed up under a different sort order ID(51) than the one currently running on this server(52), and the least one of them is a non-binary sort order.
Is there any way that I can re-config sort order ID without re-install my new sql server 7.0?
March 25, 2002 at 1:55 pm
I dont think so. Or at least running rebuildm to change the collation. One area where SQL2K is really nice!
Andy
March 26, 2002 at 5:15 am
This is untested and I do not guarantee it will work but it may.
Run sp_helpsort to see the current information about sort order / collation however this is just for information to see what SQL understands.
Now what you really want to compare is this on both servers
Run
select * from master.dbo.syscurconfigs where config IN (1123,1124,1125)
select * from master.dbo.sysconfigures where config IN (1123,1124,1125)
Both should return the same. Sample Output
value config comment status
----------- ------ -------------------------- ------
52 1123 default sortorder id 0
1033 1124 Unicode locale id 0
196609 1125 Unicode comparison style 0
They key is the values for each. Now on the new server try this and see what happens (Again this is untested and just a theory which may fail but that means you will have to do reinstall anyway. But let me know what happens please.)
--Configure server to allow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE
GO
--Set your new sort order and collation to match
UPDATE master.dbo.sysconfigures SET value = OldServersValue WHERE config = 1123
GO
UPDATE master.dbo.sysconfigures SET value = OldServersValue WHERE config = 1124
GO
UPDATE master.dbo.sysconfigures SET value = OldServersValue WHERE config = 1125
GO
--You may not need this but better to try and know works than not and nothing works
UPDATE master.dbo.syscurconfigs SET value = OldServersValue WHERE config = 1123
GO
UPDATE master.dbo.syscurconfigs SET value = OldServersValue WHERE config = 1124
GO
UPDATE master.dbo.syscurconfigs SET value = OldServersValue WHERE config = 1125
GO
--Configure server to disallow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '0' RECONFIGURE WITH OVERRIDE
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 27, 2002 at 5:45 am
Correction to all that read this
First note 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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply