SQL Server: Syntax near keyword 'CREATE' when batch executing CREATE SCHEMA and CREATE TABLE

I'm trying to set up an SQL Server 2008 database using JDBC (latest version). I have a list of setup sql commands which I'd like to execute all together:

Basically what I do is:

connection.setAutoCommit(true);
stmt = connection.createStatement();
stmt.addBatch("CREATE SCHEMA test")
stmt.addBatch("CREATE TABLE test.a (x integer)")
stmt.executeBatch();

this throws:

Incorrect syntax near the keyword 'CREATE'.

If I execute those commands separately, they work as expected. Would I need to add a commit here (like stmt.addBatch("COMMIT")? I'm not running a transaction, so this shouldn't be necessary.

A开发者_运维百科lso this is not an error saying test schema does not exists -- it's a syntax error and this puzzles me.

The docs only provide DML commands as an example to addBatch, but i believe batch is not limited to DML only.

Any suggestions appreciated, thanks!

m.

This is valid SQL

CREATE SCHEMA test
CREATE TABLE test.a (x integer)

This is valid

CREATE SCHEMA test
GO
CREATE TABLE test.a (x integer)

This is not valid

CREATE SCHEMA test;
CREATE TABLE test.a (x integer);

Error is

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'create'.

The reason is that create schema must be in it's own batch (Thank you Ben for pointing that out). GO is a batch separator so that is ok. ; is a statement separator within a batch and that is not ok. The first code is valid because the create table statement(s) is part of the create schema statement. They run in the same transaction as one statement.

My guess is that addBatch appends a ; at the end of each statement. Do only one call to addBatch with the entire create schema statement including create table.