sql-migrate
sql-migrate copied to clipboard
SqlServer error when a schema is set
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.