3

I am converting a Delphi program from the BDE to Advantage Database.

On weekends I work on a Win 7 machine using Delphi XE. During the week I work on a Win XP machine using Delphi 7.

Advantage tables work fine on the Win 7 machine but when copied to the XP machine they cannot be accessed - Error 5175 the index was created with a different collation sequence

I have searched the net, even gone into the Advantage forums but can't find anything helpful.

The XP and Win 7 are both set up the same as regards location etc. Table properties lists the collation sequence as blank.

Anyone know what is causing this and how to fix. I have tried dropping and recreating the indices but to no avail.

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
Graham Saint
  • 35
  • 1
  • 4
  • Jumped the gun a little. If you try and open the table Advantage Data Architect rather than just checking its properties it offers you a reindexing option which seems to fix things. So I have a workaround. I'd still like to know just what the problem is. – Graham Saint Feb 28 '13 at 08:43

1 Answers1

6

The collation sequence that is used to create or open index files depends on what collation sequence is configured for the remote server or local server you are connecting to.

For Advantage Local Server (ALS) this can be set in the adslocal.cfg config file.

The dedicated Advantage Database Server (ADS) has to be reinstalled in order to change the collation, since the installer "bakes" the collation into the server executable (at least as far as I know).

In addition to that the collation is different on different OS versions and OS languages if you select <CURRENT SYSTEM LANGUAGE> (or similar) when you install the ADS components. This is described in the help:

  1. This first option is strongly recommended and is the easier method to make sure the ANSI collation languages are the same for all connections. Specifically select an ANSI collation language when installing the Advantage Database Server and Advantage clients. Make sure to specify the same ANSI language for all installs. The ANSI collation language selected during an Advantage client install will be placed in the Advantage Local Server configuration file, ADSLOCAL.CFG.
  2. If you do not wish to use option 1 above, select for the ANSI collation language when installing the Advantage Database Server and the Advantage clients. Only select if all of the following are True. All computers used for installation of the Advantage Database Server should be running the same Windows operating system. The computer running an application that connects to the Advantage Local Server should also be running this same OS. In addition to the operating systems being the same, all computers should be using the same ANSI collation language (which is specified via the Regional Settings icon).

http://devzone.advantagedatabase.com/dz/webhelp/advantage10/master_avoiding_ansi_collation_mismatch_errors.htm

http://devzone.advantagedatabase.com/dz/webhelp/advantage10/index.html?error_5175_ae_index_collation_mismatch.htm

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
  • Hi Jens. Sorry, I should mentioned its local server. This implies the adslocal.cfg is different on the two machines. I can't check that right now as I can't access the Win 7 box but I'll check it out on the weekend. It's not clear (at least to me) why they should be different. The same install program was run on both. – Graham Saint Feb 28 '13 at 11:39
  • 3
    @GrahamSaint Which collation did you choose during installation? The problem is that if you choose "current system collation" that it differs between OS versions and OS languages ... So you should make sure to use an explicit collation. – Jens Mühlenhoff Feb 28 '13 at 11:44
  • Hi Jens, the adslocal.cfg files are identical on both machines. I just accepted the defaults when installing but apparently these work differently in Win 7 and XP. It appears Win 7 install is smart enough to know I have Australian keyboard whereas XP simply sees I have English keyboard and assumes US. When I have time I'll verify this by uninstalling and reinstalling. Thanks to everyone who responded. – Graham Saint Mar 07 '13 at 09:14
  • @GrahamSaint You're welcome. The collations are different between OS versions (i.e. XP and 7) when you select `` during installation. I added a quote with the relevant documentation to my answer. – Jens Mühlenhoff Mar 07 '13 at 10:56