Get SQL80001: Incorrect syntax near 'DEFAULT' error message in Visual Studio 2012 - by Vicky Song

Status : 

 


10
0
Sign in
to vote
ID 767010 Comments
Status Active Workarounds
Type Bug Repros 8
Opened 10/11/2012 2:14:15 AM
Access Restriction Public

Description

I create one SSDT project in VS2012, and then put the following script in the Script.PostDeployment.sql file. 

DECLARE @GuidList xml
	
SET NOCOUNT ON;
	
;WITH XMLNAMESPACES (DEFAULT 'http://myurl')
SELECT 
	 [dbo].[Contact].[Id]
FROM [dbo].[Contact]
	CROSS APPLY @GuidList.nodes('(GuidList/Guid)') GuidList(g)
WHERE (CAST(CAST(GuidList.g.query('text()') as nvarchar(50)) as uniqueidentifier) = [dbo].[Contact].[Id])

But when I build the project, I get the error message:"SQL80001: Incorrect syntax near 'DEFAULT' ". See this case: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/c0b47e5e-1809-4aee-abea-743c90886e14 

In addition, I can get the script executed successfully in database project VS2010.
Sign in to post a comment.
Posted by TonyWr on 4/21/2014 at 6:37 PM
I am experiencing this bug, but I do not believe it is Visual Studio's fault. I have copied the stored proc into SQL Management Studio and I am seeing a red squiggly line under the default keyword. On mousing over the keyword, I am seeing the message "Incorrect syntax near 'default'. Expecting ID, or QUOTED_ID."

That SQL edition in SQL Server 2008 RTM (10.50.1600.1)

I have then changed environments and put the stored procedure into a new query in that new environment, and the error goes away.

That edition is SQL Server 2012 SP1 (11.0.3000.0)

I believe that, for this environment to build successfully you need to be targeting a later version of SQL Server than what you currently have.

Posted by Fabricio Carvalho on 10/23/2013 at 5:16 AM
Maarten, this is probably due to Intellisense being disabled when you change the execution mode to SQLCMD.
I'm still able to reproduce the same issue, and I'm using Visual Studio 2013 RTM...
Posted by Maarten Jansonius on 4/16/2013 at 8:49 AM
As an additional comment: the error goes away when you change the "execution mode" to "SQLCMD"...
Posted by Maarten Jansonius on 4/16/2013 at 8:47 AM
Please reopen.
This is really very easy to reproduce, and probably quite easy to fix. yet quite annoying I'll tell you.
It's also not limited to post-deployment scripts.

All you need to do reproduced this is to use SSDT dec. 2012, target a SQL server 2008 db, and open an active definition window for a stored procedure or function that uses the "with xmlnamespaces" syntax.

Like PotSushi writes on feb. 1 2013:
CREATE PROCEDURE [dbo].[Procedure1] AS WITH XMLNAMESPACES (DEFAULT 'uri') SELECT 1;
Posted by PotSushi on 2/1/2013 at 2:20 AM
Further to my previous post I have uploaded a zipped copy of the database project 'DatabaseProjTest.zip'. I am using these versions: -

Microsoft Visual Studio Premium 2012
Version 11.0.51106.01 Update 1
Microsoft .NET Framework
Version 4.5.50709

Installed Version: Premium

LightSwitch for Visual Studio 2012 04941-004-0043007-02628
Microsoft LightSwitch for Visual Studio 2012

Office Developer Tools 04941-004-0043007-02628
Microsoft Office Developer Tools

Team Explorer for Visual Studio 2012 04941-004-0043007-02628
Microsoft Team Explorer for Visual Studio 2012

Visual Basic 2012 04941-004-0043007-02628
Microsoft Visual Basic 2012

Visual C# 2012 04941-004-0043007-02628
Microsoft Visual C# 2012

Visual C++ 2012 04941-004-0043007-02628
Microsoft Visual C++ 2012

Visual F# 2012 04941-004-0043007-02628
Microsoft Visual F# 2012

Visual Studio 2012 Code Analysis Spell Checker 04941-004-0043007-02628
Microsoft® Visual Studio® 2012 Code Analysis Spell Checker

Portions of International CorrectSpell™ spelling correction system © 1993 by Lernout & Hauspie Speech Products N.V. All rights reserved.

The American Heritage® Dictionary of the English Language, Third Edition Copyright © 1992 Houghton Mifflin Company. Electronic version licensed from Lernout & Hauspie Speech Products N.V. All rights reserved.

Visual Studio 2012 SharePoint Developer Tools 04941-004-0043007-02628
Microsoft Visual Studio 2012 SharePoint Developer Tools

NuGet Package Manager 2.0.30625.9003
NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.

PreEmptive Analytics Visualizer 1.0
Microsoft Visual Studio extension to visualize aggregated summaries from the PreEmptive Analytics product.

SQL Server Data Tools 11.1.21208.0
Microsoft SQL Server Data Tools

Web Developer Tools 1.0.30710.0
Microsoft Web Developer Tools contains the following components:
Page Inspector: Tool that offers an efficient way to decompose Web Applications and diagnose front-end issues.
Web Publishing: Extensions required for Web Publishing for both hosted servers as well as on premises.
Web Form Templates: Includes the default templates for Web Form Applications.
Editor Extensions: Includes HTML, CSS, and JS editor extensions that greatly enhance the development experience.
Posted by PotSushi on 2/1/2013 at 2:15 AM
I am experiencing this problem too, my VS2012 database project is targeting SQL2008. Using December 2012 SSDT.

Reproducible with a new SQL2008 target database project with a single object within

CREATE PROCEDURE [dbo].[Procedure1]
AS
WITH XMLNAMESPACES (DEFAULT 'uri')
SELECT 1;

Produces

Error    1    SQL80001: Incorrect syntax near 'DEFAULT'. Expecting ID, or QUOTED_ID.    D:\Users\xxxx\Documents\Visual Studio 2012\Projects\DatabaseProjTest\DatabaseProjTest\Procedure1.sql    3    21    DatabaseProjTest

The project does compile so it seems to ignore the error on build.

Regards.

Adam
Posted by Vicky Song on 10/25/2012 at 7:45 PM
The issue is not resolved now. Do you have any updates of it? To reproduce it is very simple, just copy the above script in one normal file. The same script works in VS2010, but not in VS2012.

Thanks again.
Posted by Vicky Song on 10/22/2012 at 11:14 PM
So am I, I am now still get the same error message.
Posted by Zefir on 10/18/2012 at 5:55 AM
After installing latest tools I still have this error: Just add normal script file copy and you will see this error on Visual Studio error list.
Posted by Microsoft on 10/15/2012 at 12:07 PM

Hi Vicky,

I have taken a look at the bug that you have filed in response to the forum post around errors with DEFAULT syntax in an SSDT project in VS2012. I am unable to reproduce this issue in a Post-Deployment Script in the latest bits of DACFx and SSDT in VS2012. If you have not already, please try updating to the newest version of SSDT (September 2012) available for VS2012 here - http://msdn.microsoft.com/en-us/data/hh297027. Let me know if you are still experiencing this issue or if you have any additional comments/questions.

Thanks,
Adam Mahood
Program Manager
SSDT/DACFx

Posted by Microsoft on 10/11/2012 at 10:13 PM
Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Microsoft Visual Studio Connect Support Team for triage and resolution. These specialized experts will follow-up with your issue.
Posted by Microsoft on 10/11/2012 at 2:50 AM
Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(http://support.microsoft.com)