Error in vendor statement with multiple actions

[rkosafo]

A vendor script (tsql) with multiple statements fail to execute.

For this script

vendor tsql {
    CREATE NONCLUSTERED INDEX IX_Directories_Kind ON dbo.Directories
        (
        kind
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX IX_Directories_localId ON dbo.Directories
        (
        localId
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX IX_Directories_parentDir ON dbo.Directories
        (
        parentDir
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX IX_Directories_localId_Kind ON dbo.Directories
        (
        localId,
        kind
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

alter table Closure add unique (parent, depth, child);

alter table Closure add unique (child, parent, depth);

The error is

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near the keyword 'CREATE'.'

Work around

What works in this case was to wrap each statement in a vendor statement as below

vendor tsql {
    CREATE NONCLUSTERED INDEX IX_Directories_Kind ON dbo.Directories
        (
        kind
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

vendor tsql {
    CREATE NONCLUSTERED INDEX IX_Directories_localId ON dbo.Directories
        (
        localId
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

vendor tsql {
    CREATE NONCLUSTERED INDEX IX_Directories_parentDir ON dbo.Directories
        (
        parentDir
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

vendor tsql {
    CREATE NONCLUSTERED INDEX IX_Directories_localId_Kind ON dbo.Directories
        (
        localId,
        kind
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
};

alter table Closure add unique (parent, depth, child);

alter table Closure add unique (child, parent, depth);

[rspeele]

The problem here is that GO is not actually T-SQL syntax, just a batch separator used by SQL Server Management Studio. If you try the same set of statements in an ADO.NET SqlCommand you will get the same syntax error.

Basically the reason for a batch separator is that SQL server for some bizarre reason doesn't let you combine certain types of DDL statements into a single command. That is, if you were using raw ADO.NET, you'd have to create multiple SqlCommands and run them one after the other. SSMS just splits on GO to do this.

IIRC one such statement type is CREATE VIEW. CREATE INDEX might be another, but I suspect not, seeing as you were able to get it to work with multiple vendor blocks (which don't incur batch separation). So probably it would've worked just removing the GO lines in this case.

Regardless, we do need to have some way of forcing a batch separator in a vendor block. The TSQL translator inserts them automatically for statement types that need them like CREATE VIEW, but of course it doesn't know when such statements occur within a vendor statement so there needs to be a way to do them manually.

[rspeele]

Closing in favor of #25.