|
|
Posted by Michael Hotek on 01/09/06 11:13
Because when you do not specify a schema, SQL Server first looks for the
object name under a schema which matches your login (kkhad\kimliu). When it
doesn't find an object matching that name under your schema, then it looks
in the dbo schema. If it doesn't find it there, it throws the error message
you are seeing.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"athos" <athos.liu@gmail.com> wrote in message
news:1136706308.112120.158790@g43g2000cwa.googlegroups.com...
> Hi guys, please help.
>
> What I did is :
> Step1. create a Database named [Test], it's default owner is [sa]
> Step2. create a User in Database [Test] named [kimliuTest], which maps
> to the Login [kkhad\kimliu] on this Database Server, it's a windows
> domain user.
> Step3. create a Schema in Database [Test] named [schTest], whose Schema
> Owner is User [kimliuTest]
> Step4. created two tables: [dbo].[t1] and [schTest].[t2]
> Step5. run the script
>
> ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
>
> to set the default schema.
>
> Now my problem is, when I try to run
>
> select * from schTest.t2
>
> , everything is OK, but if I run
>
> select * from t2
>
> the error message says:
>
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 't2'.
>
> why? I'm loggin in with my [kkhad\kimliu] windows domain id , Active
> Monitor also shows that. but the command does not work?
>
> Yeah, it's a good habit to write the full name of the Table with Schema
> name as prefix, however, we did our development in SQL 2000 and trying
> to upgrade to SQL2005, so trying to avoid scanning codes and inserting
> in thousand of places.
>
> How could we omit the Schema name when referrring to Tables? Thanks.
>
>
> ps. I posted in another topic days ago, but nobody is replying, when I
> tried to bring it forward by replying myself, it failed. please help me
> , thanks!
>
Navigation:
[Reply to this message]
|