August 1, 2010 at 2:02 pm
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.
August 1, 2010 at 10:11 pm
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
August 2, 2010 at 3:34 am
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.
August 3, 2010 at 8:22 am
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
August 6, 2010 at 2:04 pm
its working now guyz..thank you very much for all ur time and answers..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply