SSDT – Error SQL70001 This statement is not recognized in this context

One of the most common errors I get asked about when using SQL Server Data Tools (SSDT) Database Projects is the error “This statement is not recognized in this context”. This is actually a pretty simple error to fix.

Envision this scenario. You have a simple table:

CREATE TABLE [dbo].[Test]
( [Id] INT IDENTITY NOT NULL PRIMARY KEY
, [SomeData] NVARCHAR(20) NOT NULL
)

Great. So then you want to have a post deployment script which will populate it with some default value. Because we are following best practices we creating a post deployment script which then calls the script to populate the default data.

:r .\InsertSomeData.sql

Then we have the script InsertSomeData.sql itself:

INSERT INTO [dbo].[Test] ([SomeData])
VALUES (‘Arcane Code’)

After inserting the code, or doing a build, you get this ugly error pop up in the error window:

image

So what happened? Well, when you went to insert the script you had these options in the dialog:

image

 

If you aren’t careful, you could accidentally pick the “Script (Build)” option (highlighted in blue). This option attempts to compile and run the code as DDL (Data Definition Language, the T-SQL syntax which creates tables, indexes, etc.) syntax. Things like Insert statements though are considered DML (Data Manipulation Language) code, and aren’t eligible to be compiled as part of the project. This is what generates the “This statement is not recognized in this context” error. You are essentially putting DML code where only DDL is allowed.

But don’t despair, this is extremely simple to fix. In SSDT, simply bring up the Properties dialog for the SQL script (click in the SQL script, then View, Properties in the menu). Pick the Build Action property, and change it to None.

image

And that’s it, the error “SQL70001 This statement is not recognized in this context” should now vanish from your error list.

About these ads

2 Responses to “SSDT – Error SQL70001 This statement is not recognized in this context”

  1. Mohamed Radwan (MVP) Says:

    This was perfect, thanks for the explanation more then the solution :-)

  2. How to resolve SQL71501: User has an unresolved reference to Login in SSDT 2013 | TFSFAQ Says:

    […] SSDT – Error SQL70001 This statement is not recognized in this context […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 94 other followers

%d bloggers like this: