Forum Discussion

esobrino's avatar
esobrino
Helper II
2 years ago

Data Warehouse - SQL Database Projects Issues + 'ALTER TABLE' are not supported in Fabric

Here are some little issues:   • Selecting a Profile always revert to an “SQL Login” Authentication type … “Microsoft Entra ID – Universal with MFA support” is expected. • A batch should not only ...
data warehouse
AndyDDC's avatar
AndyDDC
Most Valuable Professional
2 years ago

Hi esobrino forgive me if you already know this but with ALTER TABLE, only certain alter statements are supported e.g. adding unique/foreign key constraints/  Altering the table column structure itself like adding/dropping columns isn't supported.  What operations are your DB projects trying to do when deploying?

  • esobrino's avatar
    esobrino
    Helper II
    2 years ago

    Thanks for your note...

     

    Yes, I know those ALTER TABLE statements that are supported and that is precisely why I point out that you can run the same exact batch by hand it it works fine.  Still when submitted through the dacpac it does not run as shown in the previous post.

     

    I can run the whole collection of scripts by hand successfully, through dacpac the same working ALTER TABLE statments fail.

     

    My expectation is that anything that does run by hand should run successfully when publishing the database through the dacpac...

     

    Your thoughts...

    • AndyDDC's avatar
      AndyDDC
      Most Valuable Professional
      2 years ago

      What ALTER statements are you running manually?

      • esobrino's avatar
        esobrino
        Helper II
        2 years ago

        Here is a partial sample of a typical successfully tested table definition within the "SQL Database Project" in "Azure Data Studio":

         

        CREATE TABLE [Case].[CaseDetail] (
           [CaseDetailNo] INT NOT NULL,
           [CaseID] VARCHAR(20) NOT NULL,
           [CaseNumber] VARCHAR(20) NOT NULL,
           [TypeID] VARCHAR(20),
           [CaseTitle] VARCHAR(128),
           [FiledDate] DATE,
           [JudgeNo] INT,
           [CourtNo] INT
        );
        GO

         

        ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT pk_CaseDetail_No
        PRIMARY KEY NONCLUSTERED (CaseDetailNo) NOT ENFORCED;
        GO

         

        ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT fk_CaseDetail_Type FOREIGN KEY(TypeID)
        REFERENCES [Case].[CaseType](TypeID) NOT ENFORCED;
        GO


        ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT fk_CaseDetail_Court FOREIGN KEY(CourtNo)
        REFERENCES [Entity].[Organization](OrganizationNo) NOT ENFORCED;
        GO


        ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT fk_CaseDetail_Officer FOREIGN KEY(JudgeNo) REFERENCES [Entity].[Officer](OfficerNo) NOT ENFORCED;
        GO

         

        Normally, when all works as expected and when you "Publish" the database it creates all objects.  Those existing objects will be dropped...  (or depends on how you set-it-up within the advance options).

         

        What I expect is that "publish" behaves the same as the similar VS "Database Project" that has been available for years...  and if it runs manually it must run through "dapac".  The ALTER TABLE limitations are OK and those declared above follow the documentation and as explained all work well in the "Data Warehouse" and manually in "Azure Data Studio"...