Collation error 5175 in a conection to Advantage ADT files

Question

I am developing an application in VB.NET which must be connected to a table Advantage (ADT)

I use the following connection string:

DSN=kantechX;DataDirectory=C:\DB\Data;SERVER=NotTheServer;
Compression=Never;DefaultType=Advantage;Rows=False;
collation=SPANISH_VFP_CI_AS_1252;AdvantageLocking=ON;Locking=Record;
MemoBlockSize=64;MaxTableCloseCache=5;ServerTypes=1;
TrimTrailingSpaces=False;EncryptionType=RC4;FIPS=False

When the connection is established the following error is generated:

Error 7200: AQE Error: State = HY000; NativeError = 5175 [iAnywhere Solutions] [SQL Advantage] [ASA] Error 5175: The index file was created with a different collation sequence than is Currently Being used. Table name: Card

We also tried the connection using the Advantage Data Architect 1110 and when the table is opened it shows this error:

Error 7200: AQE Error: State = HY000; NativeError = 5209 [iAnywhere Solutions] [SQL Advantage] [ASA] Error 5209: Different collations are used, Which can result in poor query optimization. Table collation: (none), collation index: SPANISH_VFP_CI_AS_1252 Table name: Card

Architect asks for the re-indexing of the table and when we do it, it does not show the alert 2, but when we tried to connect with ODBC from vb.net the exception maintains the first error message.

I have checked the adslocal.cfg file and I have not found anything useful

How can I solve this problem?

Answer 1

I could finally solve the problem, thanks to Jens link and other information I found on google.

Steps are as follows:

1. Make a backup of the files (just in case)

2. Install Advantage Architect in the machine where they are the ADT tables to be connected.

3. Download and install .NET Data Provider (leave the default settings for language [Current Languaje] and coding [USA]) where we have the solution for .NET

4. Download and install Advantage ODBC Driver (leave the default settings for language [Current Languaje] and coding [USA]) on the machine where we have the solution for .NET.      

   4.1 try to install the 32-bit version or 64-bit, although my system is 64-bit I had to install the 32-bit to make it work

5. Open Architect and create a new connection to existing tables (ADT file)

6. In Architect Open the table by double clicking      

   6.1 appears an alert indicating that the table is encoded in a different language and if you want to change the connection parameters (permanently, only this time or ignore), to which we say we want to permanently change (if not permanently selected, we can not perform the following steps)      

   6.2 Here one of two things happens:      

          -It displays an alert indicating that the index of the table is in another collation and recommended reindex (this is precisely what needs to be done)      

          -It indicates that the table is in use and can not be opened, in this case you must copy the file apart and repeat steps 5 and 6 for it and then overwrite the blocked one.

7. only for safety we will execute a command that allows multiple collations index EXECUTE PROCEDURE sp_AllowMultipleCollations ( '', true), the first parameter empty, '', indicates that apply to all encodings not one in particular as' Spanish_vfp_ci_as_1252 ', the second parameter, true, indicates that free restrictions.       

   7.1 to run this command look for the option to run or create manual queries with SQL in the menu of the Architect

8. Open Windows ODBC connections of the machine where we have the solution, create a new connection using the ODBC Stream Advantage conecction the ADT table, default language is ANSI, change the rating alert step 6.1, specify the configuration which is a local server.
9. In .NET we create a new ODBC conecction using a connection string specify where the created ODBC is used in Windows and the language of step 6.1

DSN = [ODBC created in Windows]; DataDirectory = [Path to the ADT file]; SERVER = NotTheServer; Compression =; DefaultType = Advantage; Rows = False; Language = SPANISH_VFP_CI_AS_1252; AdvantageLocking = ON; Locking = Record; MemoBlockSize = 64; MaxTableCloseCache = 5; ServerTypes = 1; TrimTrailingSpaces = False; EncryptionType = RC4; DDPassword = kantech; FIPS = False; TLSCiphers = AES128-SHA: AES256-SHA: RC4-MD5

  9.1 if when You run the application doesnt allow use odbc for any reason maybe you you must add the advantage DLL in the solution references (Right click in the solution folder > add references > search in the COM for Advantage) .

Answer 2

Where did you get the "collation=" setting from? As far as I know that's not an option accepted by the ADS ODBC driver.

The official documentation has a "Language=" setting, you might want to try that instead.

Language=OEM | ANSI | named collation

If this setting is provided, it overrides the CharSet setting. It can be used to specify one of the dynamic collations such as GENERAL_VFP_CI_AS_1252 for Visual FoxPro compatibility. These collations can be used with Advantage ADT tables and Visual FoxPro (VFP) tables.