Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query works in previous versions but not 2012

Author  Topic 

rwapplewhite
Starting Member

1 Post

Posted - 2014-05-01 : 14:44:21
I have a piece of software that is sending a fairly simple SELECT DISTINCT query. This query will generate an error when performed in SQL 2012 but it works fine on previous SQL versions. Here's the query:

SELECT DISTINCT e.ID, (e.FirstName + ' ' + e.LastName) AS Description FROM Employee AS e INNER JOIN EmployeeEmployeeType AS eet ON e.ID = eet.EmployeeID INNER JOIN POS2000.dbo.TimeEntry as te on e.ID = te.EmployeeID and eet.ID = te.EmployeeEmployeeTypeID WHERE (e.LoggedInArea > 0 and e.LoggedInArea <> 2) and eet.RequireLogin = 1 AND te.Logout IS NULL ORDER BY e.Description

The error that I get with this is:
-2147217900: Invalid column name 'Description'. -2147217900: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

If I take the "e" alias off of the ORDER BY field, it runs just fine, so that it obviously where my problem lies. But does anybody know why this statement would be treated differently by SQL 2012 than by previous versions? The executable that sends this query is an old VB6 app, and I would like to be able to fix this for my SQL 2012 clients without having to recompile this old legacy app. Is there a setting, perhaps a collation setting, that would make this query run in SQL 2012 as is?

Thanks!!
Ronnie Applewhite

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-01 : 16:06:16
New versions of sql are not guaranteed to have backward compatibility with any given syntax. in fact some old syntax is not valid in later versions.

the e table alias is technically wrong anyway because table [Employee] does not contain a column called [Description]. That is an expression defined in your SELECT statement. So frankly it should never have been allowed in previous versions (IMHO).

Because you are sending in-line sql from an application as opposed to calling stored procedures I think you have 3 options:
- recompile the app with syntax fixes
- don't upgrade the server version
- regress the database compatibility mode back to your previous version.


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -