167

I have 4 sql scripts that I want to run in a DACPAC in PostDeployment, but when I try to build the VS project for 3 of them I get this error:

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

The scripts contain only INSERT statements in different tables on the DB. And all of them are structured like so

IF NOT EXISTS (SELECT 1 FROM dbo.Criteria WHERE Name = 'Mileage') INSERT INTO dbo.Criteria(Name) VALUES ('Mileage'); only on different tables and with different data.

My question is why is VS complaining about 3 of them when all the scripts are the same in terms of syntax and operations?

PS: Adding 'GO' between statements as the error suggests doesn't do anything.

spottedmahn
  • 14,823
  • 13
  • 108
  • 178
Cosmin Ionascu
  • 7,018
  • 5
  • 25
  • 42

4 Answers4

431

I have found the problem. When I added the file in VS I forgot to set Build Action = None from the file properties. So changing that fixed the problem and the project now compiles.

Cosmin Ionascu
  • 7,018
  • 5
  • 25
  • 42
  • 37
    Great catch. Wow, Microsoft, seriously?? How is that error or its message intuitive in any way, shape, or form? – Mike K Nov 18 '14 at 17:45
  • 13
    Appreciate this answer, except I'm confused by the your use of "forgot" -- how in the world is one to know that's necessary in the first place? – pettys Dec 20 '14 at 17:31
  • 4
    Because I've done a similar script a while ago and someone told me about build action none :) – Cosmin Ionascu Dec 20 '14 at 18:13
  • 1
    Good catch! I haven't experienced this with other scripts I wrote...Started out of the blue. – AGS Mar 15 '17 at 19:34
  • 1
    By this way you'd make it compile but is it then available in output of project or you're using it just as storage for plain scripts? For me it looks like that this way it's just "Don't touch this file during build". – Jaroslav Kadlec Oct 28 '17 at 20:55
  • 1
    But if Build Action is None, we cannot have design mode right? – HaBo Aug 31 '20 at 16:18
  • @HaBo SQL scripts simply shouldn't be built; there's nothing that's being compiled or otherwise done with such a file. Build step None doesn't necessarily mean Not Included. – Zimano Aug 18 '22 at 11:45
18

Regardless this seems to be pretty old I stuck for some hours with that as well and I think this way could be helpful for many.

In Database project, files set as Build are considered as Database structure so just one statement is allowed in such file by design. Go nor any other batch terminator will change that behavior, that message is just mistake. More info here.

There is lot of other build options for files in such project. For your case it seems that PostDeploy. In such file you could have various commands like inserts etc.

Then you can use output of Database project as dacpac file for Data-Tier DB applications (Otherwise it's not included).

Jaroslav Kadlec
  • 2,505
  • 4
  • 32
  • 43
11

I ran into this error when using SQL Server Data Tools and it was because I had a post-deployment script that was added to the project wrong.

In the project file, any database objects should have elements like

<Build Include="dbo\tables\mytable.sql"/>

But scripts should have

<None Include="myscript.sql"/>

However I added my script file, it ended up with a Build tag instead of a None. Changing it to None fixed the error.

JamesFaix
  • 8,050
  • 9
  • 37
  • 73
-3

Remove the ;(semi-colon) from last of each statement and I am not sure but GO is not required between above insert statements.

donstack
  • 2,557
  • 3
  • 29
  • 44
  • 1
    Done that. Doesn't do anything. I know GO isn't required, but I tried it because I've wasted more than 2 hours on this. – Cosmin Ionascu Sep 09 '13 at 12:50
  • 7
    Including a semi-colon after each statement is standard syntax and has been advocated for years. There have been 2 many things that went wrong due to missing semi-colons. Advising a developer to not include a semi-colon where a semi-colon is best practice is questionable. – Brandon Apr 21 '15 at 16:36