Search

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

Resolved
as Not Reproducible Help for as Not Reproducible

6
0
Sign in
 to vote
Type: Bug
ID: 767010
Opened: 10/11/2012 2:14:15 AM
Access Restriction: Public
0
Workaround(s)
3
User(s) can reproduce this bug
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.
Details (expand)

Visual Studio/Team Foundation Server/.NET Framework Tooling Version

Visual Studio 2012

Steps to reproduce

1. Create a SSDT project in VS2012.
2. Add the script to 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])

3. Build the project.

Product Language

English

Operating System

Windows 8

Operating System Language

English

Actual results

Should build the project successfully.

Expected results

Get the SQL80001: Incorrect syntax near 'DEFAULT' error message.
File Attachments
File Name Submitted By Submitted On File Size  
DatabaseProjTest.zip (restricted) PotSushi 2/1/2013 -
Sign in to post a comment.
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)
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement