SqlServer error when a schema is set

[jonathanbp]

Hello

I'm using SqlServer 2017. When I set a schema via .SetSchema(...) I get an error;

mssql: Incorrect syntax near the keyword 'schema'

The sql produced for creating the table by sql-migrate when using schema [internal] and table [migrations] is as follows;

if not exists (select name from sys.schemas where name = '[internal]') create schema [internal];if not exists (select * from information_schema.tables where table_schema = '[internal]' and table_name = '[migrations]') create table [internal].[migrations] (\"id\" varchar(255) not null primary key, \"applied_at\" datetime) ;

It seems the create schema part must be executed in its own batch; https://stackoverflow.com/questions/5748056/why-cant-i-use-create-schema-in-a-begin-end-block-in-sql-management-studio which also works for me elsewhere. The sql statement would then be

if not exists (select name from sys.schemas where name = '[internal]') exec ('create schema [internal]');if not exists (select * from information_schema.tables where table_schema = '[internal]' and table_name = '[migrations]') create table [internal].[migrations] (\"id\" varchar(255) not null primary key, \"applied_at\" datetime) ;

which works when running it manually.