| Author |
Topic |
|
nimcreat
Starting Member
9 Posts |
 Posted - 2011-04-26 : 22:15:38
|
| Hello,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.Environment:Original was: Server 2008 R1 with SP1New is: Server 2003 with SP3All are 64 bit environments.Database Server: SQL Server 2005 SP 2 and SP3We 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 AdvanceJohnThanks In AdvanceJohn |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-04-26 : 22:36:41
|
| An index on a table will not determine how the rows are going to be returned to the caller. The only way to guarantee the order of the rows returned from a SQL statement is to use an ORDER BY clause on the select statement.On other words, if you are relying on a query like this:SELECT {some columns} FROM sometable WHERE somecriteria;And expect that query to return the data according to the clustered index order - it is not guaranteed. To guarantee the order, you need the following:SELECT {some columns} FROM sometable WHERE somecriteria ORDER BY somecolumn;If you are accessing a view - and the view is defined with an ORDER BY, that also will not work. The ORDER BY in a view is only used to determine the TOP values being returned. It does not guarantee the order of the results that the caller will get.And finally, if either of those is not your issue - we would need sample data and the query being executed from the client (stripped down if needed) where we can reproduce the issue.Jeff |
 |
|
|
nimcreat
Starting Member
9 Posts |
Posted - 2011-04-26 : 23:25:38
|
| Hello,We are not using SQL Query.We are using an external program. How do we specify ORDER BY and where is this done?If that is the case why did it work on the other server.ThanksThanks In AdvanceJohn |
|
|
|
nimcreat
Starting Member
9 Posts |
Posted - 2011-04-26 : 23:30:22
|
| Hello Again,The only thing we have changed is moved the database from one SQL server to another and the problem came up. We haven't made any program changes or anything so we can't understand why we have the problem.Thanks In AdvanceJohn |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nimcreat
Starting Member
9 Posts |
Posted - 2011-04-27 : 01:14:11
|
| Hello,Just to make to it clear it is not a problem with the external program.The problem has only come about since we moved the database NO CHANGE at allhas been made to the external program at all.We have had this problem before but we can't remember what we did to fix it. Allwe can recall is that we changed something in SQL and the problem went away.Thanks In AdvanceJohn |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-27 : 05:30:13
|
| If it worked on the other server it was purely coincidence. If a SQL query is run and there is no Order By, SQL is free to return the data in any order it chooses. There is absolutely no guarantee of order of data.Basically, you got data returned that way purely by chance, nothing more.If you want a guaranteed order, the query that returns the data to the application MUST have an Order By clause that specifies the order the data must be returned in. There is no config setting that will make SQL return the data in a guaranteed order without that.--Gail ShawSQL Server MVP |
|
|
|
nimcreat
Starting Member
9 Posts |
Posted - 2011-04-27 : 13:32:03
|
| Sorry but as we have said we are NOT using SQL query.We have changed a setting in SQL before which has meant that our program has picked up the sort order specified against the indexregardless of whether it is ascending or descending. Thanks In AdvanceJohn |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-27 : 13:43:34
|
quote:
Sorry but as we have said we are NOT using SQL query.
I'd like to know how you're getting data from a SQL Server 2005 database without using SQL. "SQL query" is NOT a program, it's the language used to query data. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-27 : 14:26:24
|
quote:
Originally posted by nimcreat
Sorry but as we have said we are NOT using SQL query.
So how are you getting the data from the database? SQL Server returns data to a client (whatever that client may be) via SQL statements, SELECT, INSERT, UPDATE, DELETE.quote:
We have changed a setting in SQL before which has meant that our program has picked up the sort order specified against the indexregardless of whether it is ascending or descending.
There is no setting in SQL Server that makes SQL return data in a particular order without the query that the application ran having an Order By on it.--Gail ShawSQL Server MVP |
|
|
|
nimcreat
Starting Member
9 Posts |
Posted - 2011-04-27 : 15:57:31
|
| As we said above we are using Visual Dataflex. Visual dataflex does not use any form of an SQL Query.Regardless of what everyone is saying about SQL queries. This worked beatuifully on our other SQL server so why should it be any different on a new installation?Thanks In AdvanceJohn |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-27 : 16:06:22
|
| As was already stated,UNLESS YOU SPECIFY AN ORDER BY CLAUSE YOU CANNOT GUARANTEE THE ORDER OF THE RESULTSThis has nothing to do with Visual Dataflex or whatever application you're using, it is a SQL Server function. In all likelihood the new server's optimizer is not using the same indexes to process the query as the old server was.Instead of repeating "we're not using SQL query" why don't you look at your Visual Dataflex options and see if you can specify an ORDER BY clause. There's no point in wondering why it worked differently on the old server. That's the only way to fix it on the new server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-27 : 16:15:13
|
quote:
Originally posted by nimcreat
As we said above we are using Visual Dataflex. Visual dataflex does not use any form of an SQL Query.
Then how does it get data back?--Gail ShawSQL Server MVP |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-04-27 : 17:11:24
|
quote:
Originally posted by nimcreat
As we said above we are using Visual Dataflex. Visual dataflex does not use any form of an SQL Query.Regardless of what everyone is saying about SQL queries. This worked beatuifully on our other SQL server so why should it be any different on a new installation?Thanks In AdvanceJohn
Since you application doesn't use SQL, this is not the place to ask these questions.You might try asking your question here:http://support.dataaccess.com/Forums/forum.phpCODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-27 : 17:18:32
|
quote:
Since you application doesn't use SQL, this is not the place to ask these questions.
It's these kinds of responses that make me wish we could vote +1 on replies.  |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-04-28 : 10:42:58
|
The "Setting" was probably creating a clustered index and reinserting all the data in order.  http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
nimcreat
Starting Member
9 Posts |
Posted - 2011-04-29 : 06:09:59
|
| Thanks for the useless reply.If this is the case i suggest you clearly put this on your website.Pretty poor if you can't support / provide some ideas on what to do.Almost 90% of programming lanugages including Microsoft ones do not use SQL Query statementsso its pretty poor not being able to offer any suggestions and basicallytelling us to get lost.Thanks In AdvanceJohn |
|
|
|
Next Page
|
SQL Sort Order Specified Not Working