How Can I Resolve Invalid Object Name Errors?
Question: I am setting myself up a Test Server for my eTail store and I’m getting these SQL errors.
My production Server (hosted externally, works just fine and always has, so it shouldn’t be the ASP code)
Here’s the error:
Number: -2147217865
Page: /Scripts/default.asp
Desc: Invalid object name
SQL: SELECT configValLong FROM storeAdmin WHERE configVar = controlRec’ AND adminType = ‘C’
- SQL 2005
- Locally, the table does exist
- If I paste the SQL statement into SQL Management Studio I get the same error BUT yet if slightly change it to ‘FROM nextinline.storeAdmin’ it works fine
and returns properly.
- If I open the ‘storeAdmin’ table then show the SQL and past in the above without changing it also works.
- Connection string defaults to the Database name and uses SQL authentication
- My guess is it’s a Server setting which (if changed) allows SQL statements to run without the Fully qualified dB name…however that is over my head.
James Delve says: SQL Server 2005 introduces the notion of “schema”, and a schema is a collection of database objects that form a single namespace. A namespace is a set in which every element has a unique name.
In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER.
If DEFAULT_SCHEMA is left undefined, the database user will have DBO as its default schema. This is why when you specify the “nextinline.storeAdmin” wording it works.
To resolve this, you can continue to use the “nextinline.storeAdmin” in your code, or simply change the user to have a default schema of “nextinline” and this will save you having to be explicit in your code.
The code to change the default schema is:
ALTER USER user_name
WITH <set_item> [ ,...n ]
<set_item> : : =
NAME = new_user_name
| DEFAULT_SCHEMA = schema_name