What is wrong with this syntak ? Why am I getting this error

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  August 10, 2016 at 11:56 am

  #328980

  RE: The following code gives the error message :

  Msg 156, Level 15, State 1, Line 6

  Incorrect syntax near the keyword 'SCHEMA'.

  -----------------------------------------------------------------

  IF not exists ( Select * FROM SYS.SCHEMAS where name = 'CLAIM' )

  Begin

  CREATE SCHEMA CLAIM

  PRINT 'Schema Created';

  End

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 10, 2016 at 12:12 pm

  #1894872

  CREATE SCHEMA must be the only statement in the batch.

  Weird enough, this is considered a single statement.

  CREATE SCHEMA CLAIM

  CREATE TABLE Claims( ClaimId int)

  CREATE TABLE OtherTable( OtherId int)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 10, 2016 at 12:15 pm

  #1894874

  mw112009 (8/10/2016)

  ---

  RE: The following code gives the error message :

  Msg 156, Level 15, State 1, Line 6

  Incorrect syntax near the keyword 'SCHEMA'.

  -----------------------------------------------------------------

  IF not exists ( Select * FROM SYS.SCHEMAS where name = 'CLAIM' )

  Begin

  CREATE SCHEMA CLAIM

  PRINT 'Schema Created';

  End

  It doesn't like the IF. Try this:

  IF not exists ( Select * FROM SYS.SCHEMAS where name = 'CLAIM' )

  Begin

  exec ('CREATE SCHEMA CLAIM');

  PRINT 'Schema Created';

  End

  ELSE

  PRINT 'Schema already Exists';