Forum Discussion
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 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.
13 Replies
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)
GO
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...
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?
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...
What ALTER statements are you running manually?
