SQL 2008 Upgrade Advisor unable to detect a deprecated Feature by SIDDHARTH_MEHTA

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	650676
Opened:	3/10/2011 10:12:16 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

1.We have a stored procedure with group by clause in a sub query on a SQL 2005 database instance.
2.As per the following link 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.

http://msdn.microsoft.com/en-us/library/ms143179.aspx

3.When we run SQL 2008 upgrade advisor it does not detect the or even give any warnings with respect to the above stored procedure.

Details (expand)

English

SQL Server 2005

SQL Engine

Win2003 Enterprise Server (SP2)

US English

1.Create the stored procedure on a SQL 2005 instance
Sample code
=========
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;
2.Execute SQL 2008 upgrade advisor against the SQL 2005 instance.

Upgrade advisor does not detect the stored procedure ior give any warning.

We would expect atleast a warning by SQL 2008 upgrade advisor for the stored procedure which is deprecated

X64

0 attachments

Posted by Microsoft on 6/29/2011 at 3:07 PM

Hello Siddharth,
After investigating the issue a bit more, we have decided not to add support in Upgrade Advisor to detect this case. The sub-query in GROUP BY clause syntax has been disallowed for a long time in SQL Server. Based on my testing, it it not accepted from SQL Server 2000 (tested on SP4) onwards. There is not much value in adding a rule for this case so I am closing this issue as "Won't Fix".

--
Umachandar, SQL Programmability Team

Posted by Microsoft on 3/11/2011 at 5:48 PM

Hi Siddharth,
Thanks for reporting the issue. We will take a look at adding a rule in upgrade advisor to detect this breaking change.

--
Umachandar, SQL Programmability Team

Microsoft