When compiling VS2010 Database Project in VS2012 I receive error SQL71566 - by Paul B Bauer

Status : 

 


16
0
Sign in
to vote
ID 765217 Comments
Status Closed Workarounds
Type Bug Repros 8
Opened 9/28/2012 9:58:40 AM
Access Restriction Public

Description

See this thread:
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/328889ed-dc38-45f4-8e72-9efc8ef84b01?prof=required

In VS 2012 I receive the following error message (one for each table) when trying to build a database project.

SQL71566: Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlFilegroup cannot not be set on both the Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable and the clustered Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPrimaryKeyConstraint.

For reference here is script associated with this error.

CREATE TABLE [dbo].[OperationsMaster](
  [OperationID] [numeric](18, 0) NOT NULL,
  [OperationName] [nvarchar](150) NOT NULL,
  [OperationTypeID] [numeric](18, 0) NOT NULL,
  CONSTRAINT [PK_OperationsMaster] PRIMARY KEY CLUSTERED 
 (
  [OperationID] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]

In above script in last line on word [PRIMARY] red line appears. When I run the same script in SSMS it executed successfully.
Sign in to post a comment.
Posted by Paul B Bauer on 3/6/2013 at 2:34 PM
For anyone else who has this problem: the response by Microsoft below is correct.

Unfortunately the latest version of SSDT is unable to model this: even though, yes, previous versions of SSDT did not have any problem with such statements.

See this other bug for more of a description and an explanation on why Microsoft should hotfix this:
http://connect.microsoft.com/VisualStudio/feedback/details/770248/error-114-sql71566

Richard Collette's workaround (at SO below and posted in Workarounds here) apparently works for some:
http://stackoverflow.com/questions/13142868/sql-server-data-tools-and-visual-studio-issue

I "solved" this issue by "fixing" the database project's SQL statements as suggested by Microsoft below (involved touching every single table declaration).
Posted by Patrick [MSFT] on 3/4/2013 at 10:02 AM
"ON [PRIMARY]" is duplicated. Although this is allowed by T-SQL SSDT cannot model it. Remove the ON [PRIMARY] from the WITH statement.
Posted by David_R_W on 1/11/2013 at 9:13 AM
Just restored
Microsoft Sql Server Data Tools - 11.1.20627.00

All errors are now gone, projects build successfully.

dacpac descriptions are spanish again, but that is minor considering problems the upgrade created.
Posted by David_R_W on 1/10/2013 at 11:49 AM
Just starged getting this issue. Recently updgraded to Visual Studio Premium 2012
Microsoft Visual Studio Premium 2012
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50709

and had no issues compiling or deploying the updated database project.

Just installed
SQL Server Data Tools 11.1.21208.0
Microsoft SQL Server Data Tools

in an attempt to not have spanish descriptions of all my dacpac files.

Now I get the above error on every single table in any of my database projects.

Updating the table scripts is not feasible, we have 1000s of tables spread across many database projects in different libraries, schema comparisons to keep application projects updated with shared library projects, etc.

Error only experienced on my computer after updating Sql Server Data Tools. MS build proccesses on build server are fine as well.

Also noted, after installing Sql Server Data Tools, each database project I open is listed as needing migration when first opened, even projects already updated to VS 2012.

This particular migration makes the following changes to my sqlproj files:

<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
<PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">10.0</VisualStudioVersion>
    <PostConversionRemoveFromScc>
    </PostConversionRemoveFromScc>
    <PostConversionAddToScc>
    </PostConversionAddToScc>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <DacVersion>2.1.0.0</DacVersion>
</PropertyGroup>

becomes:

<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
<PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">10.0</VisualStudioVersion>
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">10.0</VisualStudioVersion>
</PropertyGroup>
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
<PropertyGroup>
    <PostConversionRemoveFromScc>
    </PostConversionRemoveFromScc>
    <PostConversionAddToScc>
    </PostConversionAddToScc>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <DacVersion>2.1.0.0</DacVersion>
</PropertyGroup>
<PropertyGroup>
Posted by TexasCodeMonkey on 12/12/2012 at 2:43 PM
Open the table(s) designer in the db project that are having the error. Remove the primary key and add it back. Fixed it for me.
Posted by Nilkanth on 11/14/2012 at 2:51 AM
Hello,
     Any update in this regards. Can we check new release of SSDT for November Update? If any workaround also please update here. As now this has become quite old issue.

Nilkanth
Posted by James Thorpe on 11/12/2012 at 2:53 AM
I see the same issue inside VS2010 after installing a more recent version (November '12) of SSDT. It was fine prior to this update.
Posted by thedudeCL on 11/1/2012 at 1:19 PM
Hey Microsoft folks, what's the good word here? (been a while since we've heard anything)
Posted by NerdBrick on 10/31/2012 at 10:54 AM
Has any progress made on this issue? We are having the same issue.
Posted by Nilkanth on 10/6/2012 at 9:31 PM
Hello,
        Please download from following skydrive link FileNAme TestDB.RAR

https://bay145.mail.live.com/default.aspx?id=64855#!https://skydrive.live.com/?cid=e2112a155deecea6!cid=E2112A155DEECEA6&id=E2112A155DEECEA6%21113

This is a VS2012 solution.

Nilkanth S. Deasai
Posted by Helen [MSFT] on 10/5/2012 at 1:23 AM
Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Visual Studio Product Team for triage and resolution. These specialized experts will follow-up with your issue.
Posted by Paul B Bauer on 10/1/2012 at 2:58 PM
Here is a project that demonstrates the issue: http://sdrv.ms/V7kFBs
Posted by Helen [MSFT] on 10/1/2012 at 12:20 AM
Thank you for submitting feedback on Visual Studio and .NET Framework. In order to efficiently investigate and reproduce this issue, we are requesting additional information outlined below.

Could you please give us a demo project to demonstrate this issue so that we can conduct further research?

We look forward to hearing from you with this information.

Microsoft Visual Studio Connect Support Team
Posted by Macy [MSFT] on 9/30/2012 at 6:21 PM
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)