471,050 Members | 1,125 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

How to mit the Schema name when referrring to Tables?

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!

Jan 9 '06 #1
0 1383

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by N. Shamsundar | last post: by
7 posts views Thread by Robert Stearns | last post: by
16 posts views Thread by Bob Stearns | last post: by
2 posts views Thread by David++ | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.