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

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

esobrino — Sat, 27 Apr 2024 12:53:36 GMT

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 be allowed to have one statement on scripts. For example, it is possible that some variables may be needed if you are trying to populate a table. Alternatively, allow the script not to be executed (is not clear how to do this). In VS the Database Projects allow for such…
• It will be nice to add support for VS Database Projects to publish to Fabric… I tried and was clear how to…

More important...

While publishing it outputted an issue while failing to deploy the dacpac:
Error: Could not deploy package. Error SQL0: Deployment cannot continue because table changes that require ‘ALTER TABLE’ are not supported on Synapse Data Warehouse in Microsoft Fabric at this time.

That I know is not true since I can execute the scripts that do have those 'ALTER TABLE'(s) without any issues and I get the expected results in the Fabric Data Warehouse.

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

AndyDDC — Sun, 28 Apr 2024 20:25:32 GMT

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?

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

esobrino — Mon, 29 Apr 2024 11:35:34 GMT

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

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

AndyDDC — Mon, 29 Apr 2024 11:54:12 GMT

What ALTER statements are you running manually?

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

esobrino — Mon, 29 Apr 2024 16:16:53 GMT

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

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

Anonymous — Tue, 30 Apr 2024 18:03:08 GMT

Hi esobrino
Thanks for using Fabric Community.
The internal team replied as follows:

We have an artificial block on all alter table operations right now. You raise a good point that constraints can be added using alter table but unfortunately since we've implemented this block we cannot add constraints at this time through SQL projects. This block will be lifted in the coming semester.

Apart from this I could not understand this point from the first question:

A batch should not only be allowed to have one statement on scripts. For example, it is possible that some variables may be needed if you are trying to populate a table. Alternatively, allow the script not to be executed (is not clear how to do this). In VS the Database Projects allow for such…

Can you please explain this?

Thanks

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

esobrino — Tue, 30 Apr 2024 18:27:54 GMT

Got it!

Related to the batch issue... Within the "SQL Database Project" I wrote some code to load data into a table... some thing like:

DECLARE dt DATETIME2(6) = getutcdate()

INSERT INTO [Code].[StatuteTitleCode] (CodeID, Description, UpdatedDate) VALUES

('AB', 'Alcoholic Breverages', dt)

The previous runs as expected within its script when executed by hand, but in dacpac it states that statements must be followed by a "GO". And there is no point on putting a GO after the DECLARE since I will loose the variable. This case is a no brainer since I can just put the date directly on the values... still it will be nice that a batch could be more than just one sentence.

Your thoughts...

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

Anonymous — Fri, 03 May 2024 18:03:41 GMT

Hi esobrino
Is this behavior specific to DW or is it across SQL projects? and is this script a post deployment script?

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

Anonymous — Fri, 03 May 2024 18:38:11 GMT

Can you give the above details esobrino ?
Thanks

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

Anonymous — Mon, 06 May 2024 09:47:31 GMT

Hi esobrino
We haven’t heard from you on the last response and was just checking back to see if you can provide the details asked above.
Thanks

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

Anonymous — Tue, 07 May 2024 17:48:10 GMT

Hi @esobrino
We haven’t heard from you on the last response and was just checking back to see if you can provide the details asked above.
Thanks

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

esobrino — Tue, 07 May 2024 18:57:45 GMT

Hi,

As stated above, I have (lets say) this script with one batch:

DECLARE dt DATETIME2(6) = getutcdate() INSERT INTO [Code].[StatuteTitleCode] (CodeID, Description, UpdatedDate) VALUES ('AB', 'Alcoholic Breverages', dt) GO

When I "publish" to deploy the model it complained about having 2 statements (1 - the declare, and the 2nd the insert) and say that each should be in a separate batch implying that a GO should be in between.

I commented the above and try to deploy ("publish") it and have other issues stating that it can't drop "exec_request_history" and others that are not even my objects. I should not had those issues since I guess I suppose to supress or ignore those default objects using the following profile:

<?xml version="1.0" encoding="utf-8"?> <Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <TargetDatabaseName>DwStructured</TargetDatabaseName> <TargetConnectionString>Data Source=txttshydoloejkit7jdka6ptg4-rfn4od4lgaduto2fljvpzsg4tu.datawarehouse.fabric.microsoft.com;Initial Catalog=DwStructured;User ID="Eduardo Sobrino - Eduardos@estradaconsultinginc.com";Password=******;Connect Timeout=30;Encrypt=True;Trust Server Certificate=False;Authentication=ActiveDirectoryInteractive;Application Name=azdata;Command Timeout=30</TargetConnectionString> <AllowIncompatiblePlatform>True</AllowIncompatiblePlatform> <DropConstraintsNotInSource>False</DropConstraintsNotInSource> <DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource> <DropExtendedPropertiesNotInSource>False</DropExtendedPropertiesNotInSource> <DropIndexesNotInSource>False</DropIndexesNotInSource> <DropPermissionsNotInSource>True</DropPermissionsNotInSource> <DropObjectsNotInSource>True</DropObjectsNotInSource> <DropRoleMembersNotInSource>True</DropRoleMembersNotInSource> <DropStatisticsNotInSource>False</DropStatisticsNotInSource> <IgnoreKeywordCasing>False</IgnoreKeywordCasing> <IgnoreSemicolonBetweenStatements>False</IgnoreSemicolonBetweenStatements> <AllowDropBlockingAssemblies>True</AllowDropBlockingAssemblies> <ProfileVersionNumber>1</ProfileVersionNumber> </PropertyGroup> </Project>

My guess is that those default objects should not be touched and that by setting "DropXXXNotInSource" those should not be managed. Still when I generate the script I got:

PRINT N'Dropping View [queryinsights].[exec_requests_history]...'; GO DROP VIEW [queryinsights].[exec_requests_history]; GO PRINT N'Dropping View [queryinsights].[long_running_queries]...'; GO DROP VIEW [queryinsights].[long_running_queries]; GO PRINT N'Dropping View [queryinsights].[frequently_run_queries]...'; GO DROP VIEW [queryinsights].[frequently_run_queries]; GO PRINT N'Dropping Schema [_rsc]...'; GO DROP SCHEMA [_rsc]; GO PRINT N'Dropping Schema [queryinsights]...'; GO DROP SCHEMA [queryinsights];

That I want to avoid being dropted or even managed.

Sorry to show another issue while explaining the original found issue with the 2 statements batch.

If I could have any existing documentation to perform a successful publish that will be nice, else if you help me prepare such documentation it will also be OK.

Hope to hear from you soon...

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

Anonymous — Wed, 08 May 2024 17:13:38 GMT

Hi esobrino

Apologies for the issue you have been facing.
I will request you to please go ahead with Microsoft support on this. 
https://support.fabric.microsoft.com/en-US/support/
Our dedicated support team is well-equipped to handle code-related inquiries. They can assist you in troubleshooting the issue and provide you with step-by-step guidance.
After creating the ticket, please provide the details here.

Thanks

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

esobrino — Wed, 08 May 2024 19:22:21 GMT

Support # is: 2405080040012467