Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tbode2013
Starting Member
2 Posts |
 Posted - 2013-04-05 : 11:51:58
|
| I have been brought back into a project that I worked on 6 years ago. During my absence, the client upgraded from SQL 2000 to SQL 2005. I had several views that were called from a VB6 application that returned records sorted on an insertion date. The view, when brought into a query window and executed, returns ordered data. The same view when referenced in vb with additional conditions and submitted, does not return the sort. What changed in 2005? What additional information can I provide for an answer? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-05 : 11:57:26
|
| Views/Sets do not have order so the view is ignoring the ORDER BY because it doesn't realy make any sense. If you want a particular order you need to add an ORDER BY to the SELECT from the view, not the view itself.You might be able to "hack" it by putting a TOP 100 PERCENT in the SELECT int he view. But, I'd fix the flaw rather then try to hack it. |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-05 : 15:23:27
|
| To add to what Lamprey suggested, prior to SQL 2005, if you had an order by clause in views, the data was returned ordered, but starting in SQL 2005, the order is not guaranteed. I can't find the MSDN reference for that change, but this page written by Itzik Ben-Gan (whose articles I consider just as reliable as an MSDN pages) mentions that change http://sqlmag.com/t-sql/deprecated-t-sql-features |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
tbode2013
Starting Member
2 Posts |
Posted - 2013-04-06 : 21:17:15
|
| Thank you all for answering. I thought 2005 was the culprit but did not have a reference. I can handle the data myself in VB now that I know. |
|
|
|
|
|
|
|
|
Views not executing when called from VB