Build Error on CTAS Statements

[joearusinfo]

Type: Bug

I have a database project created for a datawarehouse in Microsoft Fabric. I have a CTAS statement within a stored procedure which is working in Fabric Datawarehouse but in SQL Database Project it throws build error.

Extension version: 1.3.1 Azure Data Studio version: azuredatastudio 1.46.0 (39449bbe88a0bc4092c9b205cad10d0a556beffd, 2023-09-16T01:40:10.706Z) OS version: Windows_NT x64 10.0.22621 Restricted Mode: No Preview Features: Enabled Modes:

System Info

|Item|Value| |---|---| |CPUs|11th Gen Intel(R) Core(TM) i5-1135G7 @ 2.40GHz (8 x 2419)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|15.65GB (4.08GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

[kisantia]

@joearusinfo if you try extracting a dacpac, is that successful or is there also an error? Can you please share what the error is?

[joearusinfo]

The error message I see is the generic error "Incorrect Syntax Near SELECT".

Example Code:

CREATE TABLE dbo.MyTestTable AS SELECT * FROM testdbo.dbo.SourceTable

I'm not sure how we can extract a dacpac for a Microsoft Fabric Warehouse DB.

[kisantia]

To extract a dacpac, the best way would be to use the extract action of sqlpackage, which is the DacFx command line tool (sql projects uses DacFx to build and publish the project). Please use the /df parameter when running the sqlpackage extract to collect logs.

I'm not sure if CTAS in a stored proc is currently supported in DacFx, so trying to use sqlpackage will help with determining if it is.

[joearusinfo]

Extracting Dacpac is successful... Is it possible to use it to build the database project successfully?

[kisantia]

@ssreerama fyi

[ssreerama]

HI @joearusinfo ,

If extracting the dacpac is successful in DacFx, but failing in sql project, we should need to take a look at it.

Also, It would be easy if could you please provide the sample project (if possible) to us. Thanks

[joearusinfo]

Please find the attached sample project. Test CTAS.zip

[ssreerama]

@joearusinfo , Thank you for the project, I was able to repro this with both SDK version and legacy project also. Will take a look at it and update you. Thanks

[joearusinfo]

@ssreerama , did you get any chance to look at it?

[joearusinfo]

@ssreerama , @kisantia Did you get any chance to check this?

[ssreerama]

Hi @joearusinfo , Sorry, was able to repro the issue but did not get a chance to look into it yet. Is it blocking you from your work, if so, is it possible to manually delete or comment the CTAS statements and do the build and deploy till we fix this issue. Thanks.

[joearusinfo]

@ssreerama , Do you have any ETA for this issue. Since CTEAS are easy to use in many places and used in many places in the project. This is becoming a blocker for us to deploy the database through SSDT project.

[dzsquared]

Hi @joearusinfo - I've moved this issue over into the dacfx repo, so it's closer to where the fix needs to be checked in. In full transparency, our next release is in the next few weeks and although this item is a priority it will not likely be in that release. It is, however, lined up for the release later this spring.

[joearusinfo]

@dzsquared , Thanks for the update.

[NathanNZ]

@dzsquared is this still estimated for release by the end of spring? Or is this something that might fall into the next quarter?

[joearusinfo]

@ssreerama , glad it is fixed. so how can I apply it on Azure Data Studio? should I update any extension?

[ssreerama]

@joearusinfo , currently this fix can be seen in SqlPackage and DacFx only. You can see this in ADS with its upcoming release.