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?