Visual Studio thinks correct SQL syntax is incorrect

Question

In Visual Studio 2013, I have created a database project and imported an existing database. At first all was fine, and the project built and produced create scripts.

However, now Visual Studio seems to think that there are SQL syntax errors, returning several SQL46010: Incorrect syntax near: errors. These are all from code generated by VS - I have not written any of it.

Example 1:

ALTER ROLE [db_owner] ADD MEMBER [SomeUser];
SQL46010: Incorrect syntax near ADD.

Example 2:

DECLARE @Foo NVARCHAR(7) = 'abcdefg';
SQL46010: Incorrect syntax near =.

If I copy/paste the code into SSMS, everything works fine.

Unfortunately, these prevent the project from building, which means I can't publish. Since they are errors, not warnings, I cannot set them to be ignored in the project settings.

A workaround exists where I can set the problem files' Build Action to None, but I need these files included when I publish.

I have tried:

• Deleting and re-adding the same code
• Copy/pasting code to new SQL file
• Closing and reopening the solution
• Closing and reopening Visual Studio
• Updating Microsoft SQL Server Data Tools (SSDT)
• Updating Visual Studio

The closest problem I have been able to find is this MSDN thread from 2012, stating that there is a bug SSDT SQL parser (which prompted me to try updating SSDT).

Pogrom

Answer 1

There is a setting in the project properties called Target platform that tells VS what version of SQL Server to check the syntax against.

These are relatively new additions to the T-SQL syntax, and they might not work if the syntax was set to e.g. SQL Server 2005.

Wachtel

this does not work for me my project is set for latest version of VS and SQL server. what is the fix. I am using ADS not SSMS since I am on Mac – Enterotomy

For my project, the target platform was already 'SQL Server 2016'. For me, the solution was 'Clean' the project and redo again. – Brine

Answer 2

Old thread but this did catch me out so I thought I'd share my experience, the documentation for ALTER ROLE seems to indicate that it is supported on SQL 2008 when in fact only part of the functionality is. You can use Alter Role to change the name of a Role i.e. ALTER ROLE [ROLE NAME] WITH [NEW ROLE NAME] But to add or remove members from the role it is necessary to use the old (and now deprecated) stored procedure sp_addrolememeber i.e. sp_addrolemember 'Role Name', 'User Name'.
As Aaroninus points out if you target SQL 2012 or later this will not report as an error.

Tepid

Answer 3

In my case I was adding already existing post deployment script(.sql file) by clicking on a folder named "Reference Data" ==>Add Existing Item==>Select List of .sql files including the Script.PostDeployment1.sql file. On pressing F5 it was giving following error:

SQL46010: Incorrect syntax near :.

contents of sql file were :

:r ".\Customer.data.sql"
GO

:r ".\Product.data.sql"
GO

:r ".\OrderType.data.sql"
GO

:r ".\Orders.data.sql"
GO

Solution: Post Deployment script should be created using VS.In existing project,open Visual studio and add post deployment script by clicking on : Add==>New Item ==>Script ==>Post Deployment Script ==>.PostDeployment1.sql.After creating PostDeployment1.sql file simply copy paste your content an then press F5..PostDeployment1.sql should execute correctly without any error.

Riding

This was my issue as well. I had confirmed that the file type in the project was pre/post deploy, but it looks like it didn't include an additional property in the .sqlproj file after all that's crucial for pre/post scripts e.g. <"PreDeploy Include="xxx" /> – Mckinley

Answer 4

having to set a flag in the project is really stupid since i've got ddl sql scripts in both oracle and mssql inside my c# class project.

Gosport

Answer 5

You also get this error when you add a new class file to your project, e.g. "createtable.sql.cs" by mistake and rename it to "createtable.sql".

Right click the file, then properties and select None in Build actions.

Willner