Database cannot be loaded due to different sort order

[Duy_Le_-_Islet_]

Hi All,

I tried to load the database backup from a production system to a test system to perform system refresh. However, I faced the following error:

Could you please explain what is correct sort order to use and how can we correct the issue?

I would be very grateful for any feedback.

Best regards,

Duy

[Former Member · Accepted solution]

1> select  sortorder=id,charset=csid,soname=name,description=convert(varchar(60),description)

2> from syscharsets

3> where csid = 190

4> and id in (25,99)

5> go

sortorder charset soname                         description

--------- ------- ------------------------------ ------------------------------------------------------------

        25     190 binary                         Binary ordering for UTF-16

        99     190 binaryalt                      Alternate UTF-16 binary ordering for UTF-8

(2 rows affected)

So, in order to restore the database, you need to change the sort order of the target instance to binaryalt (or create a new ASE instance with this charset/sortorder).

This can be done on an instance which doesn't have any user data in it yet by using the "sqlloc" utility.  Documented in the utility reference guide.

[Duy_Le_-_Islet_ · Accepted solution]

Hi Kevin,

I found that information from the production and test system already. Basically, the sort order of test system is 25 while the sort order of production is 99. What I don't understand is why this happen, could you please provide some clues?

Anyhow, we'll need to load the database of production system to test system to perform the system refresh. Could you please suggest the solution for this error?

Best regards,

Duy

[Former Member · Accepted solution]

If your test system is a different sort order, you can not load the production dump to it.

How did that happen?  Well, I can only predict that the DBA who installed your test system didn't set the same sort order as your production system.  If your test system is meant to be a copy of your prod system, that would be a major problem.  No production databases could be loaded to your test system.  Are you saying that previous dumps of prod have been successfully loaded into test, and now all of a sudden they don't load?

So, if you want to restore your production dump, you either have to change your test system sort order, or create a new test system ASE instance with the same locale settings as the production system.

[Duy_Le_-_Islet_ · Accepted solution]

Hi Kevin,

Thanks for your reply. This is the first time we performed system refresh, so no database load from production database to the test system. The Basis member who installed the system stated that there is no option to choose the sort order during installation, so it's unclear why the sort order is different.

Anyway, I have changed the test system sort order, than loaded the database of production system. The system refresh has been done now.

Thanks for your contribution. Have a nice day

Best regards,

Duy

[Former Member · Accepted solution]

Glad to hear your problem is resolved. 

I'm certainly not aware of how your Basis team member did the install, but i've run the installation of SAP/Sybase ASE maybe hundreds of times over the past 20+ years, and I can assure you that choosing a character set and sort order is most certainly a step in the process.

Have your Basis team member refer to the installation guide:

http://help.sap.com/saphelp_ase1602_instsol/helpdata/en/a6/42ebb9bc2b10149298956ef6dfc8e3/content.ht...

Good luck -