Unable to select from table without adding schema name

Question

Unable to select from table without adding schema name

kingdonshel

SSCommitted

Points: 1704
More actions
September 21, 2012 at 8:27 am

#393229

I have server Admin password, however even when I choose the AdventureWorks Database from the dropdown and run the below query I get an errror messg.
eg select * from ProductPhoto gives the following error messg.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductPhoto'.
However if I were to type this query
select * from Production.ProductPhoto
I am able to get a result set.
This never use to happen prior. Does anyone know why this is happenning.
I have tried to create Synonyms, however I cant name the synonym the exact name of the table.
I dont want to always type a schema name when making queries, does anyone know why this happens.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

There's a cautionary note in the CREATE SCHEMA section of BOL;

"Beginning with SQL Server 2005, the behavior of schemas changed. As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In such databases you must instead use the new catalog views. The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. For more information about catalog views, see Catalog Views (Transact-SQL)."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

You should have to provide the schema. It prevents issues with naming collisions.

I can't remember if this worked in 2005 for me, but in 2008 and R2, this hasn't worked for me. Even as an admin, I always need to provide the schema name if it's not the dbo schema.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

kingdonshel (9/21/2012)
I have server Admin password, however even when I choose the AdventureWorks Database from the dropdown and run the below query I get an errror messg.
eg select * from ProductPhoto gives the following error messg.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductPhoto'.
However if I were to type this query
select * from Production.ProductPhoto
I am able to get a result set.
This never use to happen prior. Does anyone know why this is happenning.
I have tried to create Synonyms, however I cant name the synonym the exact name of the table.
I dont want to always type a schema name when making queries, does anyone know why this happens.

You will have to qualify object names with the schema name anytime you reference an object that is not in the default schema for that database user.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Michael Valentine Jones (9/21/2012)
kingdonshel (9/21/2012)
I have server Admin password, however even when I choose the AdventureWorks Database from the dropdown and run the below query I get an errror messg.
eg select * from ProductPhoto gives the following error messg.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductPhoto'.
However if I were to type this query
select * from Production.ProductPhoto
I am able to get a result set.
This never use to happen prior. Does anyone know why this is happenning.
I have tried to create Synonyms, however I cant name the synonym the exact name of the table.
I dont want to always type a schema name when making queries, does anyone know why this happens.
You will have to qualify object names with the schema name anytime you reference an object that is not in the default schema for that database user.

Also from BOL:

"Important:

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. "

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Michael Valentine Jones (9/21/2012)

kingdonshel (9/21/2012)
I have server Admin password, however even when I choose the AdventureWorks Database from the dropdown and run the below query I get an errror messg.
eg select * from ProductPhoto gives the following error messg.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductPhoto'.
However if I were to type this query
select * from Production.ProductPhoto
...

When you logon onto server as "Admin", your default schema, most likely, is dbo, therefore all queries against objects within other schemas, will fail until you specify their schemas explicitly.

...
This never use to happen prior. Does anyone know why this is happenning.
...

It didn't happen before because one of the following:

1. You (or anyone else) used to logon onto the server with account which has default schema set to "Production"

2. You had the same table within dbo schema.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

This is one of the reasons it is generally considered best practice to always include schema when referring to any object. And if the schema is not in dbo it is almost mandatory.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

Sean Lange (9/21/2012)
This is one of the reasons it is generally considered best practice to always include schema when referring to any object. And if the schema is not in dbo it is almost mandatory.

Also, it gives you some tiny performance gain as server doesn't need to do schema look up.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Eugene Elutin (9/21/2012)
Sean Lange (9/21/2012)
This is one of the reasons it is generally considered best practice to always include schema when referring to any object. And if the schema is not in dbo it is almost mandatory.
Also, it gives you some tiny performance gain as server doesn't need to do schema look up.

Yeap, that is underlying benefit. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kingdonshel SSCommitted Points: 1704 More actions · Answer 9

Thanks guys, managed to create a synonym for the table using the same name of the table, no when I select from it I get the results I need.

query now works.

select * from ProductPhoto

CREATE synonym ProductPhoto

for AdventureWorks.Production.ProductPhoto

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Multi-tenant databases that employ the 'single-database, schema-per-tenant' model is one case where leaving the schema out of a query is appropriate. Other than that I have to agree on all previous points, schema-qualifying your objects should be the default development behavior.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato