SQL71006 - Why Am I Getting This Error?

Question

I have the following table defined in my SQL project:

CREATE TABLE [Global].[Coater]
(
[CoaterId] [int] NOT NULL IDENTITY(1, 1),
[EquipmentId] [int] NOT NULL,
[Number] [int] NOT NULL,
[LastModifiedTime] [datetime2] NOT NULL CONSTRAINT [DF_Coater_LastModifiedTime] DEFAULT (getdate()),
[LastModifiedUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Coater_LastModifiedUser] DEFAULT (suser_name()),
[VersionNumber] [timestamp] NOT NULL,
CONSTRAINT [PK_Coater] PRIMARY KEY CLUSTERED  ([CoaterId])
)
ALTER TABLE [Global].[Coater] ADD
CONSTRAINT [FK_Coater_Equipment] FOREIGN KEY ([EquipmentId]) REFERENCES [Global].[Equipment] ([EquipmentId]);
 
 
CREATE NONCLUSTERED INDEX [IX_Coater_EquipmentId]
    ON [Global].[Coater]([EquipmentId] ASC);
 
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Global Coater data.', @level0type = N'SCHEMA', @level0name = N'Global', @level1type = N'TABLE', @level1name = N'Coater';
 

When I build my project, I am getting the following error:

Error 132 SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.

I don't understand why I am getting this error. And, adding GO batch separators doesn't help.

Thank you.

---

Randy Minder

Answer 1

Hi randy

how do you know the error pertains to this table?

this error is usually (in my experience) due to having a script in your project with property BuildAction=Build whereas it should have BuildAction=None. Try that.

regards

jamie

---

ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 2

Within the code you create the table and directly alter it.

please try as follows

CREATE TABLE [Global].[Coater]
(
[CoaterId] [int] NOT NULL IDENTITY(1, 1),
[EquipmentId] [int] NOT NULL,
[Number] [int] NOT NULL,
[LastModifiedTime] [datetime2] NOT NULL CONSTRAINT [DF_Coater_LastModifiedTime] DEFAULT (getdate()),
[LastModifiedUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Coater_LastModifiedUser] DEFAULT (suser_name()),
[VersionNumber] [timestamp] NOT NULL,
CONSTRAINT [PK_Coater] PRIMARY KEY CLUSTERED  ([CoaterId])
)
GO


ALTER TABLE [Global].[Coater] ADD
CONSTRAINT [FK_Coater_Equipment] FOREIGN KEY ([EquipmentId]) REFERENCES [Global].[Equipment] ([EquipmentId]);

GO 
 
CREATE NONCLUSTERED INDEX [IX_Coater_EquipmentId]
    ON [Global].[Coater]([EquipmentId] ASC);
 
GO

EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Global Coater data.', @level0type = N'SCHEMA', @level0name = N'Global', @level1type = N'TABLE', @level1name = N'Coater';
 

But I think Visual Studio query editor has some limitation about executing multiple batches

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 3

But I think Visual Studio query editor has some limitation about executing multiple batches

This has nothing to do with the editor, also you are not executing the statement from the editor, this error happens at compile time. The BATCH seperators are used to provide scoping when resolving object shapes. SSDT or VSDB scripts for that matter serve a different purpose, they provide the blueprint of the model you want, and do not represent how it will be used to construct the target site. We choose TSQL as our DSL, at first we wanted to use a different representation, but user research in 2005 told us loud and clear users wanted to use TSQL as their DSL. In order to make the compiler work, we had to impose some simplifications, one is a more frequent use of BATCH seperators to indentify object scopes.

Hope that explains.

---

-GertD @ www.sqlproj.com