Share via

SQL71006 - Why Am I Getting This Error?

  • Article

Question

Monday, February 11, 2013 7:16 PM | 2 votes

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

All replies (7)

Wednesday, February 13, 2013 3:10 AM ✅Answered | 1 vote

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

Monday, February 11, 2013 9:18 PM | 21 votes

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

Tuesday, February 12, 2013 12:31 PM

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

Thursday, January 16, 2014 9:40 PM | 5 votes

The batch seperator GO is the solution for this. But as a remark, I will give some credit to Jamie as well :)

This error is coming when you try to add "insert - scripts" for adding data to your table and the build action is set to "build".  But it is very irretating to add loads of GO statements between every INSERT statement just. Instead, you should add the script will all your INSERT statements, but you should change the "build action" to "postdeploy". This will result in you script to be executed after the database is created and the database will have your data from the script when you have published the database project.

Roar Jørstad aka sveroa
Senior Consultant, EVRY as
Blog: Notebook, trick & tips
Please mark as answer or helpful if my post is useful

Friday, May 30, 2014 6:48 PM

This fixed the problem for me. Thanks!

Wednesday, February 8, 2017 7:03 PM

This is the answer if you have a sql script in your project that you are referencing to load objects AFTER the build.

Otherwise your script needs to be properly broken up by batch operators.

Thursday, April 19, 2018 1:58 PM

Hi Randy,

I agree with Jamie set the build action to None,the issue will be resolved.

I faced this issue when I was trying to update data using multiple update scripts.

Regards,

Ganesh