Why does SQL Server query works differently on different servers? -

i have query behaving differently when run against 2 databases.

i have copied staging database locally , using debug code.

here's query :

select distinct ........, acs.lastname bs.name branchname, ..... ...... ...... ) left join companytrove  bs on ......................... ............................ order acs.lastname,bs.branchname, ..... asc 

companytrove table has no column called branchname. so, fails on local copy of database. when run same query against actual database runs fine though companytrove table on database not have branchname column (it same table of course).

any ideas on missing ?

see

behaviour changes database engine features in sql server 2005

specifically order clause section.

the sql server 2000 behaviour

column names in order clause resolved columns listed in select list, regardless if qualified. example, following query executes without error:

use pubs select au_fname 'fname',   au_lname 'lname' authors order a.lname 

sql server ignores qualifier in order clause , resolves column name lname select list.

so presume trying on database in 2000 compatibility mode when worked without complaint.