SQL 2008 R2 Upgrade Advisor is not detecting Breaking Changes - by SBCmac

Status : 

 


5
0
Sign in
to vote
ID 736644 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 4/12/2012 2:42:17 PM
Access Restriction Public

Description

SQL 2008 Upgrade Advisor is not detecting Breaking Changes.   For example, the group by breaking change that is outlined in the below MSDN article is not detected.

http://msdn.microsoft.com/en-us/library/ms143179(v=sql.105).aspx

-----------------------------------------------------------------------------
--Excerpt from MSDN article
-----------------------------------------------------------------------------
GROUP BY

The GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. In earlier versions of SQL Server, this was allowed. In SQL Server 2008, error 144 is returned.

For example, the following code will succeed in SQL Server 2005 and fail in SQL Server 2008.


DECLARE @Test TABLE(a int NOT NULL);
INSERT INTO @Test SELECT 1 union ALL SELECT 2;
SELECT COUNT(*) 
FROM @Test
GROUP BY CASE WHEN a IN (SELECT t.a FROM @Test AS t)
THEN 1 ELSE 0 
END;
-----------------------------------------------------------------------------

Long story short, we need to know if we can rely on the SQL 2008 R2 Upgrade Advisor to identify breaking changes.   We are aware of the changes that Upgrade Advisor does not detect, which are listed in the Upgrade Advisor Help under “Other Database Engine Upgrade Issues”.   But this item is not listed as one of the items that are not detected by Upgrade Advisor.
Sign in to post a comment.
Posted by Microsoft on 1/11/2013 at 9:14 AM
Hi SBCmac

Thanks for contacting Microsoft. We have been able to reproduce this issue, but decided not to fix the underlying problem. Therefore I am resolving this issue at this time.

Regards
-Sam Hughes
Posted by Joe [MSFT] on 6/1/2012 at 4:03 PM
Thanks for reporting this. We are looking at addressing the issue in the next servicing release of Upgrade Advisor.