0

I have a SQL Select query that's embedded in a piece of C# code which I don't want to change. My problem is that the query executes fine on SQLServer 2008 but not 2012.

The offending line of code is:

Select code as SiteCode from TimeSheetContracts S order by S.SiteCode

Executed in a database on SQL2008 it works fine. The same database upgraded to SQLServer 2012 errors with the following...

Msg 207, Level 16, State 1, Line 2
Invalid column name 'SiteCode'.

If I edit the query to be 

Select code as SiteCode from TimeSheetContracts S order by SiteCode

it works fine. Can anyone explain this?

user2707324
  • 1

1 Answers1

2

There is no column in TimeSheetContracts called SiteCode, so a reference to s.SiteCode is not valid. Aliasing in ORDER BY is a little more strict since SQL Server 2000, when the syntax was a little more forgiving. The only way s.SiteCode would have worked on your SQL Server 2008 instance was if your database was in COMPATIBILITY_LEVEL = 80 (go ahead and try it on a different database that is 90 or greater). Once you move to SQL Server 2012, 80 is no longer an option. On a 2005, 2008 or 2008 R2 instance, try this:

CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.SalesOrderHeader(SalesOrderID INT);
GO
SELECT SalesOrderID AS ID FROM dbo.SalesOrderHeader AS h ORDER BY h.ID; -- fails
GO
ALTER DATABASE floob SET COMPATIBILITY_LEVEL = 80;
GO
SELECT SalesOrderID AS ID FROM dbo.SalesOrderHeader AS h ORDER BY h.ID; -- works
GO
USE master;
GO
DROP DATABASE floob;

If you want to use the column alias, you'll need to (and should always have been) just use the alias. If you want to use the table alias prefix, you'll need to use s.code.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490