SQL Sort Order Not Working

Last Post 05 May 2011 09:10 PM by SwePeso. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
New Member
New Member

26 Apr 2011 07:34 PM

We have a program written in Visual Dataflex however despite the index showing in SQL 2005 as ascending the data shown in our program is not reflecting that.

We had this running on SQL Server 2005 on another server and it was fine. But this issue has occurred since we were forced to move everything to a new machine. We tried a fresh install of SQL Server 2005 on another machine and had exactly the same problem. We have tried using the latin1_CI_AS collation and SQL_LATIN1_CI_AS as default collation but neither has made any difference.

Original was: Server 2008 R1 with SP1
New is: Server 2003 with SP3
All are 64 bit environments.
Database Server: SQL Server 2005 SP 2 and SP3

We have tried service packing SQL but to no avail. We've tried different collations as above. The studio enforces that ARTHIBORT must be set to on in SQL in order to use uppercase indexes.

We have done a thorough search on the internet and can't find any clues as to how to solve this issue so any suggestions would be greatly appreciated.

Thanks in advance

Thanks In Advance

New Member
New Member

27 Apr 2011 06:28 AM
Do you use order by clause in your queries?
New Member
New Member

27 Apr 2011 06:01 PM
rm is correct in that without specifying a ORDER BY clause SQL Server is free to return the data in what ever order it decides to process it in. That can be affected by many factors. If you had it returned in a specific order (seemingly) before you got lucky. If you do have an ORDER BY clause can you show the exact query and maybe a few samples of te rows it doesnt sort properly?
New Member
New Member

05 May 2011 09:10 PM
Also see http://www.sqlteam.com/forums/topic..._ID=159951
and http://www.sqlservercentral.com/Forums/Topic1099185-149-1.aspx
and http://www.sswug.org/forums/tm.aspx?m=8125
and http://social.msdn.microsoft.com/Forums/en-CA/sqldatabaseengine/thread/4394fb01-070f-4034-887f-5d9c9c7da36b
and http://www.sqlmag.com/community/forums/aft/96742

Everyone tells the same thing; You must specify an ORDER BY in the outermost query.

Acceptable Use Policy