SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Views not executing when called from VB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tbode2013
Starting Member

USA
2 Posts

Posted - 04/05/2013 :  11:51:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/05/2013 :  11:57:26  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 04/05/2013 11:58:21
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 04/05/2013 :  15:23:27  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/06/2013 :  03:04:57  Show Profile  Visit jezemine's Homepage  Reply with Quote
even top 100 percent will not guarantee that it will work.

see: http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx

you must put the order by in the query that selects from the view.


elsasoft.org
Go to Top of Page

tbode2013
Starting Member

USA
2 Posts

Posted - 04/06/2013 :  21:17:15  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000