SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query works in previous versions but not 2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rwapplewhite
Starting Member

USA
1 Posts

Posted - 05/01/2014 :  14:44:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 05/01/2014 :  16:06:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000