why can not build sql server project on VS with this object

Question

If execute against database it works! but trying build on Vs sql server project it doesn't.

CREATE TABLE [dbo].[Recebimento_Arquivo] (
    [RAr_Codigo]       INT                        IDENTITY (1, 1) NOT NULL,
    [Rar_ID_FS]        UNIQUEIDENTIFIER           DEFAULT (newid()) ROWGUIDCOL NOT NULL,
    [Rec_Codigo]       BIGINT                     NULL,
    [FAr_Codigo]       INT                        NOT NULL,
    [RAr_Arquivo]      VARBINARY (MAX) FILESTREAM NULL,
    [RAr_Dat_Cadastro] DATETIME                   CONSTRAINT [dnfRecebimentoArquivo_RArDatCadastro] DEFAULT (getdate()) NULL,
    CONSTRAINT [PK_Recebimento_Arquivo] PRIMARY KEY CLUSTERED ([RAr_Codigo] ASC) ON [FG_ARQUIVOS_DADO],
    CONSTRAINT [FK_Recebimento_Arquivo_FAr_Codigo] FOREIGN KEY ([FAr_Codigo]) REFERENCES [dbo].[Formato_Arquivo] ([FAr_Codigo]),
    UNIQUE NONCLUSTERED ([Rar_ID_FS] ASC) ON [FG_ARQUIVOS_DADO]
) FILESTREAM_ON [FG_ARQUIVOS_FS_01];

It results

Severity    Code    Description Project File    Line    Suppression State
Error       SQL71566: Filegroup: [FG_ARQUIVOS_FS_01] cannot not be set on both the Table: [dbo].[Recebimento_Arquivo] and the clustered Primary Key: [dbo].[PK_Recebimento_Arquivo].

Clustered index/key is table itself - most likely you cannot set filegroup for it separately at all. — Arvo
– Arvo, Commented Mar 8, 2017 at 13:57
if I just omit filegroup on primary key it work, but need understand why expliciting filegroup on primary it doesn't work! — Agnaldo Junior
– Agnaldo Junior, Commented Mar 8, 2017 at 14:15
Problem is not primary, but clustered key. Clustered key is data (table) itself and it cannot be located in another filegroup than table one (they are the same data) - and likely sql server doesn't allow to set it in this case at all. — Arvo
– Arvo, Commented Mar 8, 2017 at 14:27

Hugo Kornelis · Accepted Answer · 2017-03-08 14:53:18Z

1

At first sight, looks to be a bug in VS SQL Server projects.

As other comments say, you cannot speecify different filegroups for clustered key and for table. But that's not what you do. The final filegroup specification is for the filestream data that you store in the varbinary(max) column. Based on the syntax description in Books Online, this should be valid - and the fact that this runs when you execute it on SQL Server directly confirms that.

answered Mar 8, 2017 at 14:53

Hugo Kornelis

2512 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Agnaldo Junior Over a year ago

Is true this is a bug in Visual Studio to solve my problem in my project what I did was just omit the FileGroup in my primary key and the project compiled normally

Abhinav · Accepted Answer · 2017-03-08 13:43:49Z

0

Ans is in your error itself,

    Severity    Code    Description Project File    Line    Suppression State
Error       SQL71566: Filegroup: [FG_ARQUIVOS_FS_01] cannot not be set on both the Table: [dbo].[Recebimento_Arquivo] and the clustered Primary Key: [dbo].[PK_Recebimento_Arquivo].

you need to have different FileGroup and Primary Key Identifier.

edited Mar 8, 2017 at 13:43

user170442

answered Mar 8, 2017 at 13:42

Abhinav

1,2001 gold badge8 silver badges12 bronze badges

3 Comments

Agnaldo Junior Over a year ago

They are differents!

Abhinav Over a year ago

check other tables

Agnaldo Junior Over a year ago

They are differents! Table filegroup is FILESTREAM_ON [FG_ARQUIVOS_FS_01], and Primary key filegroup is [FG_ARQUIVOS_DADO].

Collectives™ on Stack Overflow

why can not build sql server project on VS with this object

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related