| Author |
Topic |
|
poser
Posting Yak Master
124 Posts |
 Posted - 2010-09-24 : 11:50:29
|
| We are in the process of moving off of SQL2000 to SQL2005 SP3.We have a lot of views that have the 'order by' in them. In a perfect world we would change them...Is there any way to get these to work? I have read article kb 926292 but that seems to apply to SP2?Thanks for any and all help!R/P |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 12:18:23
|
| You can use SELECT TOP 100 PERCENT, which will stop you getting an error, however the Sort Order is not guaranteed (not sure it ever was TBH, it may work most/nearly-all the time) |
 |
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2010-09-24 : 12:22:57
|
| We need the sort order to work in the views. I found that it is fixed in sql2008 http://support.microsoft.com/kb/956717But I can't find a fix for sql2005?Thanks, P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 12:34:47
|
| Interesting. Apparently it did work as expected in SQL 2000.The patch you refer to in KB 956717 is the same one as per your original post - i.e. http://support.microsoft.com/kb/926292/Surprised this was a HotFix to SQL2005 SP2 and then NOT included in SP3 ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 12:43:21
|
| Can you not migrate to SQl2008? All that effort fixing and regression testing .... and then you only get to something already several years old and coming to the end of its supported life I expect (SQL 2000 lifecycle ended 3 years after SQL2005 came out, or perhaps it was version-after-next - i.e. when SQL2008 launched. Either way, its "soon"). Personally I would want to leapfrog to SQL2008 and have the benefit of the migration testing for that much longer ... plus all sorts of useful new features to be able to take advantage of. |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2010-09-24 : 12:43:36
|
So I am really confused now...Is there a fix for SQL2005 SP3? P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 12:49:03
|
| "Is there a fix for SQL2005 SP3?"I didn't see one. But if SP3 came out AFTER the HotFix I would expect it to include it.I presume you have tried it and its bust?If you are just going by warnings from Upgrade Advisor, or somesuch, then I think it is worth trying it in SQL2005 SP3.If you are migrating, and haven't see it already, this may be of interest:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2010-09-24 : 13:07:32
|
| We have tested and the views are not ordered correctly.<sigh>SQL 2008 is not an option for us yet....Thanks for your helpR/ P |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2010-09-24 : 16:22:47
|
| I just see something else happening with this that is troubling:In the query analyzer if I have a view with Select Top 100 percent and a order by, it will not order correctly.If I add the Select top (select 100) percent with a order by, it is correct.If I call on the view that displays correctly in the query analyzer via .asp it is not ordered correctly.Any ideas?Thanks P |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-24 : 16:30:20
|
| From the KB article it appears to happen only with TOP 100 PERCENT, try TOP 999999999999 instead. (no PERCENT) |
|
|
|
|
Order By views in SQL2005 SP3