Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

error while trying to move objects from dbo to new schema Expand / Collapse
Author
Message
Posted Sunday, August 1, 2010 2:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 10:08 AM
Points: 12, Visits: 38
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.

Post #961925
Posted Sunday, August 1, 2010 10:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 1,618, Visits: 20,979

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
Post #961981
Posted Monday, August 2, 2010 3:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:45 PM
Points: 23, Visits: 109
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.
Post #962046
Posted Tuesday, August 3, 2010 8:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:36 AM
Points: 965, Visits: 958
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
Post #962857
Posted Friday, August 6, 2010 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 10:08 AM
Points: 12, Visits: 38
its working now guyz..thank you very much for all ur time and answers..
Post #965449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse