locked
New error after upgrading to September SSDT RRS feed

  • Question

  • I have upgraded to the newly release version of SSDT for VS2012. Before upgrading my solution built and published without any errors. After the upgrade I get multiple error "SQL71567: Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlFilegroup cannot be specified on Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable when there is the clustered Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPrimaryKeyConstraint on the table."

    On all of these tables the ON [PRIMARY] filegroup specification is specified. This has been working for years, why has this become an error on the new release?


    Jeremy Hartley

    Monday, September 17, 2012 12:08 PM

Answers

  • Hi Jeremy, this error is expected – we took a fix in this most recent release that blocks setting filegroup, filestream, partition scheme, and data compression options on both a table and a clustered index or non-inlined clustered constraint on that table, because when options are specified on both, the options specified on the table are ignored by the engine. We plan to clean up the error message in a future release to make it easier to understand.

    This change in behavior by DacFx reflects the actual behavior of SQL Server.  The fix resides in your T-SQL code where you should remove the duplicate setting of the options on both your table and index/constraint.

    Monday, September 17, 2012 5:33 PM

All replies

  • Hi Jeremy, this error is expected – we took a fix in this most recent release that blocks setting filegroup, filestream, partition scheme, and data compression options on both a table and a clustered index or non-inlined clustered constraint on that table, because when options are specified on both, the options specified on the table are ignored by the engine. We plan to clean up the error message in a future release to make it easier to understand.

    This change in behavior by DacFx reflects the actual behavior of SQL Server.  The fix resides in your T-SQL code where you should remove the duplicate setting of the options on both your table and index/constraint.

    Monday, September 17, 2012 5:33 PM
  • I'm getting the same issue, but I only have the filegroup being assigned in the table definition, and not in the PK or Clustered Index definition.  Are you saying that when there is a clustered index, the filegroup assignment has to be on PK/Clustered Index?

    Wednesday, September 26, 2012 1:41 PM
  • It is best to place the Filegroup definition on the Clustered Index, since the Clustered Index definition takes precedence over the table definition. However you should still be able to define the FileGroup on the Table definition in SSDT if
    a) The clustered index is defined inline, inside the Create Table statement

    b) The clustered index does not include a FileGroup definition.

    Hence this will work:

    CREATE TABLE [dbo].[Table1] (
        [Id] INT NOT NULL,
        CONSTRAINT [pk1] PRIMARY KEY CLUSTERED ([Id] ASC)
    ) ON [OtherFg];

    However if the Index is defined separately - in its own Create Index statement or by Altering the Table, then it will cause the same error.

    Let me know if this solves your problem,

    Kevin

    Thursday, September 27, 2012 9:22 PM
  • I wish we had been aware of this breaking change before upgrading to the latest release of SSDT. The update created hundred of errors (more than 300 in one database alone). We have 4 enterprise databases across 5 active branches, and in 6 environments. To fix that many objects would take numerous CRs and hundred of hours of work just for our single enterprise application. The error seems like something that should be able to be suppressed.  So, sounds like it's best not to upgrade? Ouch.
    Wednesday, October 31, 2012 2:04 PM
  • I've created a PowerShell script to help with this scenario.It removes the table specification of the filegroup where a specification exists in an index or constraint file for the table.

    You can access the script at https://gist.github.com/4498559 

    Comments/feedback/forking welcome. 

    Thursday, January 10, 2013 1:22 AM
  • Hi Jeremy, this error is expected – we took a fix in this most recent release that blocks setting filegroup, filestream, partition scheme, and data compression options on both a table and a clustered index or non-inlined clustered constraint on that table, because when options are specified on both, the options specified on the table are ignored by the engine. We plan to clean up the error message in a future release to make it easier to understand.

    The implication is that there is a bug in the engine - that you cannot request that the index and table be placed on separate specified filegroups, and have it excuted correctly.

    And that your solution to this bug is to display a badly written error message.

    How about fixing the bug in the engine to run the SQL we have written as intended , and create the tables as the developer intended ?

    The you don't need the stupid error and confusing error message.

    • Edited by Barmy Army Thursday, June 6, 2013 12:55 PM
    Thursday, June 6, 2013 12:54 PM
  • Hello, this brings up and interesting point though.  What is the best practice, say from the performance perspective.  Eveything I have read says to separate out clustered indexes.  How is specifying a filegroup on the table and another for the index redundant.  Does that mean only the filegroup for the clustered index is used?  And this error also comes up for non clustered indexes on a primary key (uniqueidentifier). 

    Friday, June 7, 2013 4:47 PM
  • Please see "Tables and Index Data Structures Architecture

    http://msdn.microsoft.com/en-us/library/ms180978(v=sql.105).aspx

    If your table has a clustered index it represents the physical structure, therefore only the clustered index in that case can be placed on a file group, the table is merely a meta data object in that case.

    Since SQL Azure started enforcing this distinction, the tooling is not enforcing it.


    -GertD @ www.sqlproj.com

    Sunday, June 9, 2013 1:54 AM
    Answerer
  • Ok so my understanding has improved, while I understood that a clustered index was used to determine how data is arranged on the disk, i didn't realise that it is implicit that it is actually stored in/with the table data.

    In which case you should not offer the option to change the filegroup on a clustered index; as any one who knows what they are doing knows its stored in the table, and the option just confuses anyone else; as the option implies the index can be stored separately to the table, but what happens is that the table changes filegroup; potentially not what the user intended.

    Allowing people to specify a filegroup on a clustered index is just a trap for the unwary, an error message explaining that clustered indexes are stored in the table file group would be educational.

    Barry


    • Edited by Barmy Army Thursday, June 13, 2013 10:07 AM
    Thursday, June 13, 2013 10:04 AM
  • This has been fixed in the latest version(s?) of SSDT. If you're still getting this error, update SSDT and retry.

    Democracy won.

    Wednesday, March 18, 2015 9:17 PM