Order by problem

[Jelly]

We have a strange behaviour in the following SQL statement:

Code: Select all

SELECT 
    TruckPlanningList.VBELN_VL, TruckPlanningList.ActivationDate, HostState.HostStateId,  HostState.Name AS HostStateName
FROM  
    TruckPlanningList with(nolock) 
INNER JOIN
    HostState with(nolock) ON TruckPlanningList.HostStateId = HostState.HostStateId
order by 
    HostState.Name, TruckPlanningList.ActivationDate

In the query Analyzer of the SQL Server 2000, it gets to the following, correct result:

Code: Select all

VBELN_VL   ActivationDate                                         HostStateId          HostStateName                                      
---------- ------------------------------------------------------ -------------------- -------------------------------------------------- 
ABCDE12346 2006-11-20 14:50:05.977                                1                    Active
ABCDE12345 NULL                                                   0                    Inactive

(2 row(s) affected)

If I run EXACTLY THE SAME statement in a TSimpleDataset in Delphi, using the dbexpress driver for MSSQL (dbxsda200.exe), the order is wrong. It gets to the following:

Code: Select all

VBELN_VL   ActivationDate                                         HostStateId          HostStateName                                      
---------- ------------------------------------------------------ -------------------- -------------------------------------------------- 
ABCDE12345 NULL                                                   0                    Inactive
ABCDE12346 2006-11-20 14:50:05.977                                1                    Active

(2 row(s) affected)

Is this a bug?

[Jackson]

We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem;
it is desirable to use Northwind or Master schema objects, otherwise include definition of your own database objects; don't use third party components.

Also supply us following information
- Exact version of Delphi or C++ Builder
- Exact version of DbxSda. You can see it in ReadMe.html
- Exact version of Microsoft SQL Server and OLE DB provider that you use.
You can see it in version info of SQLOLEDB.DLL and SQLNCLI.DLL.

[Albervan]

Hi, great day!

We are migrating our application to the dbExpress MSSQL driver from Devart. Previously we used the native driver for Delphi 7.

In this process of migration we are evaluating the impacts of this change in the operation of our application.

The first impact identified is exactly as mentioned in Jelly's post, regarding the ordering of our queries.

Without any other change, just changing the connection driver, several of our queries were messed up. I would like to understand why this different behavior and how to solve it without needing a massive change in all our query screens (+ - 1300 source code files).

[Stellar]

In order for us to analyze the issue, please provide us with the query that demonstrates it, as well as a script for creating tables.

[Albervan]

We're solving this problem in the topic:
viewtopic.php?f=10&t=24794