error while trying to move objects from dbo to new schema

  • 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.

  • 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

  • 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.

  • 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

  • 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