SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Transfer table to Schema
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mallorz4
Starting Member

27 Posts

Posted - 03/05/2014 :  19:44:47  Show Profile  Reply with Quote
I created a schema, Admin. I have to transfer a table from the dbo schema to the admin schema. I keep getting an error that I do not have permission or the table does not exist.

Simply looking for confirmation here - is my syntax correct?

ALTER SCHEMA Admin TRANSFER MyShop.Addresses;

(MyShop is the Database, Addresses is the table)

NOTE: When I created the schema, I did not create an inner table. The syntax for that was simply
CREATE SCHEMA Admin;


Thank you.

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 03/05/2014 :  20:08:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
USE MyShop
GO
ALTER SCHEMA Admin TRANSFER dbo.Addresses;


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 03/05/2014 :  20:20:44  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

USE MyShop
GO
ALTER SCHEMA Admin TRANSFER dbo.Addresses;


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thanks for the reply. I get the same result.

I expanded the Databases tree and dug into MyShop. While all of the other tables in the database read dbo.X, the Addresses table reads Admin.Addresses. Is it possible I somehow altered that while messing with this statement? If so, how can I change it back?

I'll be back at this tomorrow night, probably signing off for the evening.


Edited by - mallorz4 on 03/05/2014 20:28:47
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 03/05/2014 :  22:18:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
If it says Admin.Addresses, isn't it already where you want it to be?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 03/06/2014 :  06:52:27  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

If it says Admin.Addresses, isn't it already where you want it to be?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Should it say that under the MyShop database then? I never got a 'successful' execution which is concerning. Maybe I should confirm with my instructor ..?
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/06/2014 :  11:16:57  Show Profile  Reply with Quote
To verify your success, write a query including Admin.Addresses. If it succeeds, then bingo.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 03/06/2014 :  17:24:57  Show Profile  Reply with Quote
quote:
Originally posted by maunishq

To verify your success, write a query including Admin.Addresses. If it succeeds, then bingo.

=======================
Not an Expert, Just a learner.
!_(M)_!



SELECT * FROM Admin.Addresses;
.. Fails (Invalid object name)

USE MyShop
SELECT * FROM Admin.Addresses;
Succeeds with contents of Addresses table.

This would be the same as saying..

USE MyShop
SELECT * FROM Addresses;


.. so what's the point of transferring the table to the schema? I suppose this is all correct then? I guess I don't completely understand this exercise.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 03/06/2014 :  18:29:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Your first one fails because you aren't in the correct database. If you want to query it from the wrong database, you need to supply the database name: select * from MyShop.Admin.Addresses

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 03/06/2014 :  18:36:53  Show Profile  Reply with Quote
Okay. Syntax-wise it looks right to me and it seems like it is so I'll roll with it.

Thank you everyone for the replies!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000