I have restored an SQL 2000 database in to 2005 Standard edition.
I have several views that are displaying incorrect results though when viewed in SQL Management Studio, and hence back to Excel Word etc....
The view is as follows:
SELECT TOP (100) PERCENT DATEPART(mm, ARV_date) AS Month, COUNT(*) AS Count, SUM(DATEDIFF(mi, ARV_date, RES_date)) AS [Minutes Unresolved],
MAX(DATEDIFF(mi, ARV_date, RES_date)) AS [Max Dur(min)]
FROM dbo.ps_Remedy_Data
WHERE (DATEPART(yy, ARV_date) = DATEPART(yy, GETDATE())) AND (RMDY_Priority = 'Urgent')
GROUP BY DATEPART(mm, ARV_date), RMDY_Priority
ORDER BY Month
When I execute it from Word or Excel the returned data is NOT in Month order...
When I right click the view and say Open View it returns the same unsorted data...
When I right click the view and say MODIFY and do nothing else except EXECUTE the SQL the returned data is in the correct sequence.
This is extremely annoying and can only be described as a bug.
This is a new server running WIN 2003 SP1, with SQL Server 2005 SP1.
Thoughts appreciated, many thanks in advance
regards
Order in views make no sense, though the order by clause is only used within the use of the "TOP Something" clause to ensure that the data is ordered first to give the right data back, using TOP 100 Precent won′t have any effect on that. There is a new blog entry from Kimberly about that, perhaps you shoul have a look on that.
http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=79de45cb-2da6-4510-ae43-7e5e42e4c02e
The best way would be to treat the view as a column definition and do the order on the view.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
No comments:
Post a Comment