sql-servertfsmsbuildcontinuous-integrationdac

Visual Studio database project builds on local machine, but fails on build server

I'm trying to set up CI for a database project of mine, but am running into an issue that is stumping me. I'm able to build the project successfully on my machine; however, when I check in my changes to TFS and my build server attempts to build the project, I get errors for nearly every table and view that exists that look like the following:

Phase 1 - 11187 error(s), 556 warning(s)
SAS\dbo\Tables\CohortEntry.sql(12):SAS\dbo\Tables\CohortEntry.sql(12,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(14):SAS\dbo\Tables\CohortEntry.sql(14,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(16):SAS\dbo\Tables\CohortEntry.sql(16,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(18):SAS\dbo\Tables\CohortEntry.sql(18,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(20):SAS\dbo\Tables\CohortEntry.sql(20,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(22):SAS\dbo\Tables\CohortEntry.sql(22,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(24):SAS\dbo\Tables\CohortEntry.sql(24,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(26):SAS\dbo\Tables\CohortEntry.sql(26,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(28):SAS\dbo\Tables\CohortEntry.sql(28,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(30):SAS\dbo\Tables\CohortEntry.sql(30,1): Error SQL70001: This statement is not recognized in this context.
SAS\dbo\Tables\CohortEntry.sql(32):SAS\dbo\Tables\CohortEntry.sql(32,1): Error SQL70001: This statement is not recognized in this context.

The code within this specific file that is failing is: 

CREATE TABLE [dbo].[CohortEntry] (
    [idCohortEntry] INT           IDENTITY (1, 1) NOT NULL,
    [CohortEntry]   NVARCHAR (50) NULL,
    [CohortLevel]   NVARCHAR (1)  CONSTRAINT [DF_CohortEntry_CohortLevel] DEFAULT (N'S') NOT NULL,
    [CohortCode]    NVARCHAR (3)  NOT NULL,
    CONSTRAINT [PK_CohortEntry] PRIMARY KEY CLUSTERED ([idCohortEntry] ASC)
);


GO
EXECUTE sp_addextendedproperty @name = N'MS_DefaultView', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Contains one entry for every cohort of Students or Requests described by the SAS.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Filter', @value = NULL, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_OrderBy', @value = NULL, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_OrderByOn', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Orientation', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_TableMaxRecords', @value = 10000, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnHidden', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'idCohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnOrder', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'idCohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnWidth', @value = -1, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'idCohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'A unique serial key.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'idCohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnHidden', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnOrder', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnWidth', @value = 1815, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'A description for this cohort entry.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortEntry';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnHidden', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortLevel';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnOrder', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortLevel';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnWidth', @value = -1, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortLevel';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'A flag indicating whether this cohort entry refers to Student (S) or Request (R) cohorts.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortLevel';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnHidden', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortCode';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnOrder', @value = 0, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortCode';


GO
EXECUTE sp_addextendedproperty @name = N'MS_ColumnWidth', @value = -1, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortCode';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'The unique three-character code that defines this cohort.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'CohortEntry', @level2type = N'COLUMN', @level2name = N'CohortCode';

I'm not really sure where the issue lies - any assistance would be much appreciated.

Solution

  • I actually ended up figuring it out, partially from the earlier attached link. It looks like all of the SQL files that were causing an error were missing the GO flow operator at the end of the file - this seems to be required after every EXECUTE command, even at the end of the file.