sql server script generated by PowerDesigner

Question

I have a PDM on PowerDesigner 15.1. And I generate a script with it for a SQL Server 2008R2 db.

So the generated script looks like something like this :

if exists (select 1 
    from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')     
    where r.fkeyid = object_id('TABLE1') and o.name = CONSTRAINT1') 
alter table TABLE1
    drop constraint CONSTRAINT1
go 

This chunk of script is repeated for each table that I have.

After I have the creation of the table and the definitions of constraints.

When I want to execute this script with a DataSourceInitializer (provided by Spring), and the good JDBC driver, I've got this following error :

Failed to execute SQL script statement at line 5 of resource class path resource [create_tables_SQL_SERVER.sql]: if exists (select 1

I don't know what to do.

Answer 1

Write each statement in a single line or add a delimiter (default ;) at the end of each statement:

if exists (select 1  from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('TABLE1') and o.name = CONSTRAINT1') 
alter table TABLE1 drop constraint CONSTRAINT1

---

For completeness, because the code explain it best: The magic is done by this code in ResourceDatabasePopulator: Line 171..178

    String delimiter = this.separator;
    if (delimiter == null) {
        delimiter = DEFAULT_STATEMENT_SEPARATOR; // ";"
        if (!containsSqlScriptDelimiters(script, delimiter)) {
            delimiter = "\n";
        }
    }
    splitSqlScript(script, delimiter, statements);