[thelist] MSSQL 2000 -> 2005 change?

[Ken Schaefer]

Joel,

Who is the owner of these objects? You are using unqualified names, and that
only works if the objects are owned by the same principal (Windows account,
SQL Server account, app role etc) that the query is running under, or the
objects are owned by "dbo". If the client ran your CREATE scripts under a
separate account, then your unqualified names won't be resolvable

SQL Server 2005 also separates out the schema from the owner (in SQL Server
2000 they were pretty much one and the same). If the schema owner is not
"dbo" or the user running the query, then you need to qualify the object
names. A fully qualified name would be <server>.<schema>.<object>, which is
equivalent to <server>.<user>.<object> in SQL Server 2000.

Cheers
Ken

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Joel D Canfield
Sent: Saturday, 19 May 2007 9:41 AM
To: thelist at lists.evolt.org
Subject: [thelist] MSSQL 2000 -> 2005 change?

I have 2000, client has 2005. I've scripted the db structure, which
script ran fine on the client's box. Connected remotely, copied data
from 2000 to 2005. Tried to run this query

  select 
    e.firstname, e.lastname, e.didid, e.department, e.title, 
    o.number, 
    p.patchportnumber, 
    s.portnumber, 
    a.description 
  from
    employees e
    left join offices o on e.officeid = o.id
    left join patchports p on p.officeid = o.id
    left join switchports s on p.switchportid = s.id
    left join assets a on s.assetid = a.id

it tells me that firstname, lastname, didid, department, title, number,
patchportnumber, and portnumber are not valid fields (it does not
mention description)

query runs fine on my 2000 box with identical structure and data. I
assume this is some fun new difference with 2005.

thoughts?

thanks

joel