Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Go Back  Xtreme Visual Basic Talk > > > Creating an ordered view in SQL server 2008


Reply
 
Thread Tools Display Modes
  #1  
Old 02-16-2012, 06:12 AM
bigmonmulgrew bigmonmulgrew is offline
Regular
 
Join Date: Feb 2008
Posts: 73
Default Creating an ordered view in SQL server 2008


Hi guys
I was hoping to save some coding by using views to access my forms in VB
Instead of using a very long query I was hoping to use

Code:
Select * FROM my_view
This has the slight issue that when accessing views they are not sorted. They seem to completely ignore any ORDER BY clauses.

I've been Googling for a while and so far I've found may comments suggesting that it is not possible to sort views.
This seems wrong to me since the server management studios "New View.." page contains options to order the results

I have found details of a hotfix which is supposed to fix the problem
http://support.microsoft.com/kb/926292
Although it does require you to change some settings.
It does not work, in fact it tells me there are no components which need updating. I assume it must have come down in a service pack.

I have also found details of a workaround which suggests using
Code:
SELECT TOP (99.99) PERCENT
In my case this works but some commentators noted that with large data sets it omits a few of the records

Does anyone have a good workaround for this other than putting my query straight into my VB code.
__________________
Currently Running VB Express 2010, running on Windows 7 home premium x64
System: Gigabyte GA-MA790FX-DS5, Athlon X2 5600+, Corsair 2GB matched pair 800mhz 4-4-4-12 (epp), XFX Geforce 8600GTS 256MB, Raptor 74GB SATA, Corsair HX 630W
My Blog:http://foreverythingit.co.uk/blog
Reply With Quote
  #2  
Old 02-16-2012, 06:19 AM
DrPunk's Avatar
DrPunkCreating an ordered view in SQL server 2008 DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I think that hotfix fixes when top 100 percent with an order doesn't return in the correct order.

Top (100) percent is the only workaround I know of.
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 02-16-2012, 06:31 AM
bigmonmulgrew bigmonmulgrew is offline
Regular
 
Join Date: Feb 2008
Posts: 73
Default

Top (100) Percent seems to work when building a query.

For example If I select "New View..."
and take my first table.
In the GUI I select my primary key and a length field.
I then select to sort the primary key Descending
I get this query built
Code:
SELECT     TOP (100) PERCENT ID, Length
FROM         dbo.tblLineLog
ORDER BY ID DESC
If I click execute now the preview will show the result sorted correctly.
If I access it form VB using
Code:
SELECT * FROM my_view
It shows in the datagrid in ascending order as if it has not been sorted at all.
__________________
Currently Running VB Express 2010, running on Windows 7 home premium x64
System: Gigabyte GA-MA790FX-DS5, Athlon X2 5600+, Corsair 2GB matched pair 800mhz 4-4-4-12 (epp), XFX Geforce 8600GTS 256MB, Raptor 74GB SATA, Corsair HX 630W
My Blog:http://foreverythingit.co.uk/blog
Reply With Quote
  #4  
Old 02-16-2012, 06:46 AM
DrPunk's Avatar
DrPunkCreating an ordered view in SQL server 2008 DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Any reason you can't order it in the call?

Any particular reason that you're using a view?
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 02-16-2012, 07:09 AM
bigmonmulgrew bigmonmulgrew is offline
Regular
 
Join Date: Feb 2008
Posts: 73
Default

No massive reason.
I was using this in my VB code

Code:
Dim strqryIncompleteOrders As String = "SELECT     TOP (100) PERCENT dbo.qrySortOrders.PalletNo, dbo.qrySortOrders.PalletSize, dbo.qrySortOrders.PalletReverse, dbo.qrySortOrders.OrderStarted, dbo.qrySortOrders.PalletCardPrinted, dbo.qrySortOrders.ThicknessCode, dbo.qrySortOrders.MaxRemaining, dbo.qrySortOrders.SingleCode, dbo.qrySortOrders.Length, dbo.qrySortOrders.SingleLength, dbo.qrySortOrders.Width, dbo.qrySortOrders.SingleWidth, dbo.tblLineLog.CompleteCleared, dbo.tblLineLog.RollCut FROM dbo.qrySortOrders INNER JOIN dbo.tblLineLog ON dbo.qrySortOrders.PalletNo = dbo.tblLineLog.PalletNo GROUP BY dbo.qrySortOrders.PalletNo, dbo.qrySortOrders.PalletSize, dbo.qrySortOrders.PalletReverse, dbo.qrySortOrders.OrderStarted, dbo.qrySortOrders.PalletCardPrinted, dbo.qrySortOrders.ThicknessCode, dbo.qrySortOrders.MaxRemaining, dbo.qrySortOrders.SingleCode, dbo.qrySortOrders.Length, dbo.qrySortOrders.SingleLength, dbo.qrySortOrders.Width, dbo.qrySortOrders.SingleWidth, dbo.tblLineLog.CompleteCleared, dbo.tblLineLog.RollCut HAVING (dbo.tblLineLog.CompleteCleared = 'False') AND (dbo.tblLineLog.RollCut = 'False') ORDER BY dbo.qrySortOrders.OrderStarted, dbo.qrySortOrders.ThicknessCode DESC, dbo.qrySortOrders.SingleCode, dbo.qrySortOrders.Length DESC, dbo.qrySortOrders.SingleLength, dbo.qrySortOrders.Width DESC, dbo.qrySortOrders.SingleWidth"
Now I'm trying to use this (Where qryIncompleteOrders is the view I'm trying to create.)
Code:
"Select * FROM qryIncompleteOrders"
This is loaded onto a datagridview on the form.
My intention was that I would be able to change sort orders for example in the server management studio GUI rather than having to write the query by hand.
SQL MS also validates SQL code where as vb does not.
__________________
Currently Running VB Express 2010, running on Windows 7 home premium x64
System: Gigabyte GA-MA790FX-DS5, Athlon X2 5600+, Corsair 2GB matched pair 800mhz 4-4-4-12 (epp), XFX Geforce 8600GTS 256MB, Raptor 74GB SATA, Corsair HX 630W
My Blog:http://foreverythingit.co.uk/blog
Reply With Quote
  #6  
Old 02-16-2012, 07:13 AM
DrPunk's Avatar
DrPunkCreating an ordered view in SQL server 2008 DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Use a stored procedure instead of a view?
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008 Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
 
Creating an ordered view in SQL server 2008
Creating an ordered view in SQL server 2008
 
-->