CREATE SCHEMA AT LinkedServer

• teutales

  Say Hey Kid

  Points: 671

  More actions

  April 21, 2010 at 6:46 am

  #218143

  Hello all,

  I try to create a Backup Database on a linked Server using dynamic SQL. But I fail when I try to create a schema.

  EXEC ('CREATE SCHEMA common') AT [127.0.0.1]

  My Problem is that it is executed in the master database. I need it in my database, but I can't do something like

  EXEC ('CREATE SCHEMA databasename.common') AT [127.0.0.1]

  because the focus is on master or

  EXEC ('USE databasename; CREATE SCHEMA common') AT [127.0.0.1]

  because CREATE SCHEMA needs to be the first command.

  Do you have any idea?

  Greetz,

  Stefan

  ---------

  Edit: a way to solve my problem is to change the default database...

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  April 21, 2010 at 7:45 am

  #1155340

  teutales (4/21/2010)

  ---

  EXEC ('USE databasename; CREATE SCHEMA common') AT [127.0.0.1]

  Isn't it working fine ?

  teutales (4/21/2010)

  ---

  Edit: a way to solve my problem is to change the default database...

  i think you are right here.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• spaghettidba

  SSC Guru

  Points: 105712

  More actions

  April 21, 2010 at 8:19 am

  #1155362

  Try this:

  EXEC ('USE databasename; EXEC(''CREATE SCHEMA common'')') AT [127.0.0.1]

  It's horrible, but it should work.

  Edited: bug in code. Should be ok now.

  -- Gianluca Sartori