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.
| 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.DescriptionThe 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 OptimizerTG |
 |
|
|
|
|
|
Query works in previous versions but not 2012