Home Dashboard Directory Help
Search

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


Status: 

Closed


5
0
Sign in
to vote
Type: Bug
ID: 736644
Opened: 4/12/2012 2:42:17 PM
Access Restriction: Public
0
Workaround(s)
view
5
User(s) can reproduce this bug

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.
Details
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 Microsoft 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.
Sign in to post a workaround.