SQL Server generating code that Visual Studio claims to be an error?

Question

I have dozens of .sql file in a (poorly maintained) VS2012 SqlServer project that I'm syncing up with the database. I can't do a Schema Compare because VS complains that there are build errors. The database will have priority over what the SQL Project has, but I'm confused by the errors in one .sql in particular:

USE [ePostRxIS] /* SQL70001: This statement is not recognized in this context */
GO

/****** Object:  Table [SpyGlass].[Audit]    Script Date: 01/16/2014 11:45:59 ******/
SET ANSI_NULLS ON /* SQL70001: This statement is not recognized in this context */
GO

SET QUOTED_IDENTIFIER ON /* SQL70001: This statement is not recognized in this context */
GO

SET ANSI_PADDING ON /* SQL70001: This statement is not recognized in this context */
GO

CREATE TABLE [SpyGlass].[Audit](
    [AuditUID] [int] IDENTITY(1,1) NOT NULL,
    [AuditTypeUID] [int] NOT NULL,
    [SystemName] [varchar](25) NOT NULL,
    [ApplicationName] [varchar](250) NOT NULL,
    [Module] [varchar](250) NULL,
    [Operation] [varchar](250) NULL,
    [Description] [varchar](500) NOT NULL,
    [AdditionalInfo] [varchar](max) NULL,
    [CreatedDateTimeUTC] [datetime] NOT NULL,
    [Username] [varchar](50) NULL,
    [Synchronized] [bit] NOT NULL,
    [Event] [varchar](250) NULL,
    [EventTypeUID] [int] NULL,
    [EntityTypeUID] [int] NULL,
    [EntityUID] [int] NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
    [AuditUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON /* SQL70001: This statement is not recognized in this context */
GO
/* SQL71508: The model already has an element that has the same name SpyGlass.Audit.AuditUID.MS_Description */
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier and primary key.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'AuditUID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key into the AuditType table.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'AuditTypeUID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The machine name running the application that generated the entry.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'SystemName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the application making the entry.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'ApplicationName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The class name or "zone" of execution code.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'Module'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The web services operation that was invoked.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'Operation'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The content describing the reason for the entry.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Any additional information such as request/response XML, stack traces and so on.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'AdditionalInfo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The UTC date and time the entry was created.' , @level0type=N'SCHEMA',@level0name=N'SpyGlass', @level1type=N'TABLE',@level1name=N'Audit', @level2type=N'COLUMN',@level2name=N'CreatedDateTimeUTC'
GO

ALTER TABLE [SpyGlass].[Audit]  WITH CHECK ADD  CONSTRAINT [FK_Audit_AuditType] FOREIGN KEY([AuditTypeUID])
REFERENCES [SpyGlass].[AuditType] ([AuditTypeUID])
GO

ALTER TABLE [SpyGlass].[Audit] CHECK CONSTRAINT [FK_Audit_AuditType]
GO

ALTER TABLE [SpyGlass].[Audit]  WITH CHECK ADD  CONSTRAINT [FK_Audit_EventType] FOREIGN KEY([EventTypeUID])
REFERENCES [SpyGlass].[EventType] ([EventTypeUID])
GO

ALTER TABLE [SpyGlass].[Audit] CHECK CONSTRAINT [FK_Audit_EventType]
GO

ALTER TABLE [SpyGlass].[Audit] ADD  CONSTRAINT [DF_Audit_CreatedDateTime]  DEFAULT (getutcdate()) FOR [CreatedDateTimeUTC]
GO
/* SQL71508: The model already has an element that has the same name SpyGlass.Audit.AuditUID.MS_Description */
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Unique identifier and primary key.', @level0type = N'SCHEMA', @level0name = N'SpyGlass', @level1type = N'TABLE', @level1name = N'Audit', @level2type = N'COLUMN', @level2name = N'AuditUID';
GO
create index IDX_Audit_EntityHistory on SpyGlass.Audit (EntityTypeUID, EntityUID, Description, CreatedDateTimeUTC)
include (AuditUID, AuditTypeUID, SystemName, ApplicationName, Module, Operation, AdditionalInfo, Username, Synchronized, Event, EventTypeUID);

GO
CREATE NONCLUSTERED INDEX IDX_SpyGlass_Audit_History
ON [SpyGlass].[Audit] ([AuditTypeUID],[EntityTypeUID],[EntityUID],[CreatedDateTimeUTC])
INCLUDE (Description)

This is the contents of the .sql (with added comments indicating errors) but if I go to SQL Server Management Studio, right click the table in question (Audit), Script Table as > CREATE To, then the created script is exactly what the SQL Project has. The VS Project has the Target Platform set to SQL Server 2012 which is the proper target environment.

What can I do to make the two agree? I'm tempted to recreate the project with exactly what the database contains, get a fresh start, but if VS is complaining about generated code, what can I do? If I do correct the VS Project so it doesn't have build errors, won't the Schema Compare constantly tell me there are differences?

Answer 1

SSDT and DB Projects will only recognize the "CREATE" syntax, not the "USE" and "SET" commands. Take those out of your file for the project and you should be good.

If you're trying to put your whole DB in version control initially, consider importing it. You may also consider using the schema compare tool to compare a DB (as source) to your project (as target).