Errata¶

Sort order differences between Dataflex .DAT files and MS SQL tables¶

Description

Under certain conditions, the sort order of rows may be different for dataflex DAT files and the coverted MS SQL tables. The problem is limited to DF_ASCII columns and is caused by the different collation methods used by Dataflex and MS SQL.

Example

Let’s use the Inventory table from DataFlex Order Entry Example. First, the table is converted from DAT to MS SQL using the Inverse Key option (The Flex2SQL Utility, Inverse Key Maintenance). At this point, it is expected that the ordering of search results will be the same. However, when sorting by index.1 (Item_ID) using the DAT file, the first found Item_ID will be ACE-2, the second ACE1. When sorting by index.1 in the MSSQL table, the sorting is reversed. The first found Item_ID will be ACE1, the second ACE-2.

Inventory.dat ordered by index.1 (Item_ID)

MS SQL Inventory table ordered by index.1 (Item_ID)

This is because the collation method used by Dataflex and MS SQL is different. Dataflex is using a binary search as where MS SQL is using whatever the server is set at (typically, SQL_Latin1_General_CP1_CI_AI). Therefore, to get the MS SQL Inventory table to sort the same way as the DAT file, you need to create the MS SQL table using an sql call explicitly setting the collation type for the string (DataFlex DF_ASCII) columns. Afterwards, you need copy the data into the newly created MS SQL table. The script for creating the Invt MS SQL table would be:

CREATE TABLE [dbo].[invt](
[recnum] [bigint] IDENTITY(1,1) NOT NULL,
[item_id] [nvarchar](10) COLLATE Latin1_General_BIN NOT NULL,
[item_id_invc] [nvarchar](10) COLLATE Latin1_General_BIN NOT NULL,
[description] [nvarchar](35) COLLATE Latin1_General_BIN NOT NULL,
[description_invc] [nvarchar](35) COLLATE Latin1_General_BIN NOT NULL,
[vendor_id] [int] NOT NULL,
[vendor_part_id] [nvarchar](15) COLLATE Latin1_General_BIN NOT NULL,
[vendor_part_id_invc] [nvarchar](15) COLLATE Latin1_General_BIN NOT NULL,
[unit_price] [numeric](8, 2) NULL,
[on_hand] [int] NULL,
CONSTRAINT [invt_index01] PRIMARY KEY CLUSTERED ([item_id_invc] ASC))

CREATE UNIQUE NONCLUSTERED INDEX [invt_index00] ON [dbo].[invt] ([recnum] ASC)

CREATE UNIQUE NONCLUSTERED INDEX [invt_index02] ON [dbo].[invt] ([vendor_id] ASC,[vendor_part_id_invc] ASC,[item_id_invc] ASC)

CREATE UNIQUE NONCLUSTERED INDEX [invt_index03] ON [dbo].[invt] ([description_invc] ASC,[item_id_invc] ASC)

Note that all the string fields (nvarchar) specify the binary collation method. See collation section for more information.