CODEWITHSUNDEEP
sql-server
Agnaldo Junior
Agnaldo Junior

Reputation: 73

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

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].

Upvotes: 0

Views: 362

Answers (2)

Hugo Kornelis
Hugo Kornelis

Reputation: 251

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.

Upvotes: 1

Abhinav
Abhinav

Reputation: 1200

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.

Upvotes: 0

Related Questions