Visual Studio thinks correct SQL syntax is incorre

问题:

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).

回答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.

回答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.

回答3:

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.