HELP! ORDER BY in views no longer works in SQL2K5

Is there some setting somewhere that would break this? I have some views
with order by clauses in them that are now totally ignored. I did not write
these...I would never put an order by clause in the view but I also do not
have access to the csharp code that is calling this so I cannot move the
clause into the appropriate place.
This worked fine under SQL2000 -- is there some setting I'm missing?That was an undocumented behavior of SQL Server 2000, and
unfortunately was discovered and was put into use by many. The
problem with undocumented behaviors is that they can change without
warning, with a new release or even a service pack. This one stopped
working with SQL Server 2005.
If you want data returned in a specific order, the ONLY way to be sure
to get it is with an ORDER BY on the (outer) query.
Roy Harvey
Beacon Falls, CT
On Mon, 17 Jul 2006 16:21:32 -0700, "Tim Greenwood" <tim_greenwood AT
yahoo DOT com> wrote:

>Is there some setting somewhere that would break this? I have some views
>with order by clauses in them that are now totally ignored. I did not writ
e
>these...I would never put an order by clause in the view but I also do not
>have access to the csharp code that is calling this so I cannot move the
>clause into the appropriate place.
>This worked fine under SQL2000 -- is there some setting I'm missing?
>|||What you are seeing the the appropriate behavior. A VIEW 'should' not order
data UNLESS it is a necessary part of obtaining the required results, e.g.,
SELECT TOP 5%.
In the past, a VIEW would sometimes, but contrary to 'what should have been'
select the TOP 100% and then ORDER BY as a way to 'kludge' a sorted
resultset. TOP 100 percent and ORDER BY is ignored in SQL 2005.
In SQL 2005, it is the responsibility of the query that accesses the VIEW to
sort the data after the receiving the view resultset.
If the VIEW definitions are in the database (as they should be), you can
easily correct the view definintion. However, as you note, the application
code will have to be corrected in order for the queries to produce properly
sorted results.
This is another illustration of how having all data access operate through
Stored Procedures facilitates 'robustness'. If Stored Procedures were used,
it would be very easy for you to correct the problem.
You have few options.
Contact whoever wrote the code and request/require that it be corrected
since it was not written 'properly' and used unsupported features.
OR,
make a horrendous 'kludge'.
(1) Rename the views, and
(2) Create new stored procedures using the existing VIEW names, and in
those stored procedures, write the proper queries requesting data from the
renamed views and sorting it appropriately. (3) Test, Test, Test, Test, and
then Test.
Hopefully, someone will come up with other suggestions that will make this
outrageous kludge so difficult to stomach that you won't seriously consider
it.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23jUN%23efqGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Is there some setting somewhere that would break this? I have some views
> with order by clauses in them that are now totally ignored. I did not
> write these...I would never put an order by clause in the view but I also
> do not have access to the csharp code that is calling this so I cannot
> move the clause into the appropriate place.
> This worked fine under SQL2000 -- is there some setting I'm missing?
>|||Before everyone fires off on me, the 'horrendous 'kludge' idea was meant to
be tongue in cheek.
The joke sould have never gotten past the 'Test' stage.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:el52YxfqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> What you are seeing the the appropriate behavior. A VIEW 'should' not
> order data UNLESS it is a necessary part of obtaining the required
> results, e.g., SELECT TOP 5%.
> In the past, a VIEW would sometimes, but contrary to 'what should have
> been' select the TOP 100% and then ORDER BY as a way to 'kludge' a sorted
> resultset. TOP 100 percent and ORDER BY is ignored in SQL 2005.
> In SQL 2005, it is the responsibility of the query that accesses the VIEW
> to sort the data after the receiving the view resultset.
> If the VIEW definitions are in the database (as they should be), you can
> easily correct the view definintion. However, as you note, the application
> code will have to be corrected in order for the queries to produce
> properly sorted results.
> This is another illustration of how having all data access operate through
> Stored Procedures facilitates 'robustness'. If Stored Procedures were
> used, it would be very easy for you to correct the problem.
> You have few options.
> Contact whoever wrote the code and request/require that it be corrected
> since it was not written 'properly' and used unsupported features.
> OR,
> make a horrendous 'kludge'.
> (1) Rename the views, and
> (2) Create new stored procedures using the existing VIEW names, and in
> those stored procedures, write the proper queries requesting data from the
> renamed views and sorting it appropriately. (3) Test, Test, Test, Test,
> and then Test.
> Hopefully, someone will come up with other suggestions that will make this
> outrageous kludge so difficult to stomach that you won't seriously
> consider it.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23jUN%23efqGHA.2180@.TK2MSFTNGP05.phx.gbl...
>sql