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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

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?

Ronnie Applewhite

Flowing Fount of Yak Knowledge

6065 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
Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000