SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


error while trying to move objects from dbo to new schema


error while trying to move objects from dbo to new schema

Author
Message
brave heart
brave heart
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 40
Hi all,


I am trying to move database objects from dbo shcema to new schema in sql 2008 using

alter schema newschema transfer oldschema.objectname

I have a bunch of database objects SO i copied the above query multiple times and trying to run all of them at the same as below.


alter schema newschema transfer oldschema.objectname1
alter schema newschema transfer oldschema.objectname2
alter schema newschema transfer oldschema.objectname3
alter schema newschema transfer oldschema.objectname4
alter schema newschema transfer oldschema.objectname5

it worked for me before.but now i see some error message saying.

incorrect syntax near 'newschema' .expecting a doublecolon.

The wierd thing is when i am trying to execute one by one they are working.but when i select them all and try to run.it throws me an error.

Please someone find me a solution this is an urgent issue.thank you in advance.
Adiga
Adiga
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 21012

incorrect syntax near 'newschema' .expecting a doublecolon.


Does the error message read out "expecting a doublecolon"? Why not put a GO keyword after each statement?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
deva_etc
deva_etc
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 193
It seems the issue is specific to SQL server 2008..I tried it on SQL server 2005 and it worked just fine:

CREATE SCHEMA DBA
ALTER SCHEMA DBA TRANSFER dbo.myTable
ALTER SCHEMA DBA TRANSFER dbo.Table1

--------------------------------------------
Command(s) completed successfully.
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1826 Visits: 1117
I just ran this:

ALTER SCHEMA sk TRANSFER dbo.Names

That ran without issue on a SQL 2008 system running build 2734 (SP1 CU6 I believe).

What is the owner of your schema? dbo was the owner of my sk schema.

Steve
brave heart
brave heart
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 40
its working now guyz..thank you very much for all ur time and answers..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search