2008R2 Views with ORDER BY clause
in [Programming SQL]
|
Prev: How do I run this xpath query?
Next: A severe error occurred on the current command. The results,if any, should be discarded
From: Plamen Ratchev on 22 May 2010 17:31 On Sat, 22 May 2010 08:23:03 +0200, "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote: >Not sure how it applies to R2, but did you also apply the trace flag, as per >the KB article? And in addition it needs the database in compatibility mode 80... :) Much better to just do it right and use ORDER BY when querying the view. -- Plamen Ratchev http://www.SQLStudio.com
From: Steve Hanna on 24 May 2010 23:53 No. That has to be done before migrating the database and I'm already in production on 2008R2. "Tibor Karaszi" wrote: > Not sure how it applies to R2, but did you also apply the trace flag, as per > the KB article? > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Steve Hanna" <gtmw(a)community.nospam> wrote in message > news:3D6E6CCA-4AF8-4695-8477-9CD47122B8B1(a)microsoft.com... > > I migrated a SQL Server 2000 database to 2008R2 and now my Views don't > > return > > the rows in the order specified by the ORDER BY clause. In researching > > this > > I found this article http://support.microsoft.com/kb/926292/. I have > > applied SP1 and the cumulative package 8 for SP1 and the view still > > returns > > rows in random order. > > > > Any help would be appreciated. > > > > Thanks > > > > Steve > > . >
From: Steve Hanna on 24 May 2010 23:58 The problem is that we have hundreds of labels and the label printing software is expecting to process the view sequentially. "Plamen Ratchev" wrote: > On Sat, 22 May 2010 08:23:03 +0200, "Tibor Karaszi" > <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote: > > >Not sure how it applies to R2, but did you also apply the trace flag, as per > >the KB article? > > And in addition it needs the database in compatibility mode 80... :) > > Much better to just do it right and use ORDER BY when querying the > view. > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
From: Dan on 25 May 2010 09:49 Can you not get the developer of the label printing software to fix their application? Use of ORDER BY in a view is not part of ANSI SQL. You can read more details here as to why it doesn't work in SQL Server 2005 and 2008 here, and it was never guaranteed to work in SQL Server 2000 - it just happened that in almost all cases it did. http://blogs.msdn.com/b/queryoptteam/archive/2006/03/24/560396.aspx Dan "Steve Hanna" <gtmw(a)community.nospam> wrote in message news:E389AADE-EB96-4172-B213-352D9CAEE60A(a)microsoft.com... > The problem is that we have hundreds of labels and the label printing > software is expecting to process the view sequentially. > > "Plamen Ratchev" wrote: > >> On Sat, 22 May 2010 08:23:03 +0200, "Tibor Karaszi" >> <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote: >> >> >Not sure how it applies to R2, but did you also apply the trace flag, as >> >per >> >the KB article? >> >> And in addition it needs the database in compatibility mode 80... :) >> >> Much better to just do it right and use ORDER BY when querying the >> view. >> >> -- >> Plamen Ratchev >> http://www.SQLStudio.com >> . >>
From: Tibor Karaszi on 26 May 2010 01:51
I'm sorry, but you need the trace flag and compat 80 mode to get the old behavior. The fact is that you have an app which has a bug. It expects a behavior of SQL Server which isn't documented nor guaranteed (as witnessed). Apparently, the app doesn't support never versions of SQL Server without old compat mode and trace flag - something the app vendor should ideally be very clear about. Btw, here's my take on the subject (old blog post): http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Steve Hanna" <gtmw(a)community.nospam> wrote in message news:645D2901-5DA4-409B-8F89-7E563449B5DC(a)microsoft.com... > No. That has to be done before migrating the database and I'm already in > production on 2008R2. > > "Tibor Karaszi" wrote: > >> Not sure how it applies to R2, but did you also apply the trace flag, as >> per >> the KB article? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Steve Hanna" <gtmw(a)community.nospam> wrote in message >> news:3D6E6CCA-4AF8-4695-8477-9CD47122B8B1(a)microsoft.com... >> > I migrated a SQL Server 2000 database to 2008R2 and now my Views don't >> > return >> > the rows in the order specified by the ORDER BY clause. In researching >> > this >> > I found this article http://support.microsoft.com/kb/926292/. I have >> > applied SP1 and the cumulative package 8 for SP1 and the view still >> > returns >> > rows in random order. >> > >> > Any help would be appreciated. >> > >> > Thanks >> > >> > Steve >> >> . >> |