Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Sort order in SQL-server 7

How do I change the sort order in SQL-SERVER 7.0? I want to have the Scandinavian sort order where the extended characters (Ø, Å, Ä, and Ö) are sorted after Z.
0
polksolk
Asked:
polksolk
  • 5
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You can't change this setting, you need to reinstall SQL Server.
In order to maintain your data, you need to do the following:
1) Backup Your databases
2) Re-Install SQL Server (using binary sort order)
3) Restore Your Databases
4) Backup Your Databases
5) Re-Install SQL Server (using scandinavian Sort order)
6) Restore Your Databases

Steps 2-4 can be done on another server.
Hope this helps
0
 
polksolkAuthor Commented:
I suspected that. But before I do this I´d like to know when and where I specify the sort order so I don´t end up doing this in vain.
0
 
simonsabinCommented:
I will be amazed if that works.

Backups are sort order dependant. and so a backup on one sort order cannot be restored to another.

The standard practice is BCP the data out and then Re-Install the server using the required sort order and then BCP the data back in.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simonsabin: check up your doc, it says you can restore a database if either the backup or the server a in BINARY sort order (wink)
BTW, I got already point for another question in EE with exactly this problem (smile)
0
 
simonsabinCommented:
Well you learn something every day.

Everywhere I have read says you need to go the BCP route. I suppose its probably quicker?

1 ALL
:)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
It IS quicker, but you need to script the BCP and you shouldn't forget a table (grin).
On the other hand, you need to create the databases, tables, views, sp, users, rules, defaults.... in any case, so the backup/restore procedure is much safer...
BCP then is creating a bulk of files, whereas the Backup/restore only procudes one file, which may be handled easier.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
polksolk:
This settings is asked you during setup or SQL Server, one of the Wizard forms ask you to specify SortOrder, CodePage ...
Again, this setting has to be chosen carefully, as you can't change it without reinstalling SQL Server
BTW, AFTER reinstalling SQL Server, don't forget to review all your server-side settings like auditing, sqlmail, dtc, security, thus reviewing these items BEFORE uninstalling SQL Server.
0
 
polksolkAuthor Commented:
I couldn´t attach the database after I re-installed the SQL-server because the database and the server had different sort order. Luckily I had a backup on MSDE from which I could import the database instead.

Are you absolutely sure you can't change sort order without reinstall SQL-server?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure.
But it may be that some SQL Server developer/hacker knows how to change it, but there will be problems for your indexes that will be corrupt or at least invalid after that operation.
0
 
simonsabinCommented:
Yes positive. You try giving a japanese book to an english man and say read that. It is very similar.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now