topic Re: [Database Projects] Statement 'CREATE TABLE' is not supported in Data Warehouse

[Database Projects] Statement 'CREATE TABLE' is not supported

BiJoe — Mon, 04 Mar 2024 12:32:58 GMT

I have just started testing Database Projects extension in Azure Data Studio. I have made a new project out of an existing Fabric warehouse. In that warehouse I have several stored procedure which are triggered in a pipeline, for dropping, creating and transforming tables.

The project seemingly loads just fine in ADS, I can run select queries locally and get expected results, as well as ALTER existing procedures.

For testing I made some simple changes in one of the stored procedures in ADS, then chose Build. I get a lot of errors, the first one is

Build error SQL70015: Statement 'CREATE TABLE' is not supported for the targeted platform.

I seem to remember this being a problem earlier in Fabric warehouses, but CREATE TABLE works just fine in Fabric portal now. What am I doing wrong?

EDIT: I just made a new folder for containing all Database Projects, and created yet another new project from the same existing Fabric warehouse. I see that the changes I made in the failed build is also implemented in the stored procedure from the new project...

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

AndyDDC — Mon, 04 Mar 2024 15:18:59 GMT

Hi BiJoe so to clarify, it's not any CREATE TABLE syntax failing for actual tables in your database project, but rather CREATE TABLE syntax within Stored Procedures in your database project?

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

BiJoe — Tue, 05 Mar 2024 07:11:00 GMT

AndyDDC there are two issues here:

1. The stored procedures I wrote had CREATE TABLE AS, which I can work around by using SELECT INTO

2. All the tables, both the ones created from my SP, and the tables imported with Data Flow, show up as CREATE TABLE statements.

Each of these CREATE TABLE AS statements generate a build failure. I have submitted a support ticket for this. I guess the reason for this failure is that Microsoft SQL Server does not allow CREATE TABLE AS?

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

Anonymous — Wed, 06 Mar 2024 18:09:27 GMT

Hi BiJoe
Can you please provide the support ticket number here? This would help us to track internally.

While SQL Server itself supports CTAS, Azure Data Studio Database Projects might have limitations regarding specific platforms like Fabric warehouses. The build error message "SQL70015: Statement 'CREATE TABLE' is not supported for the targeted platform" accurately reflects the current incompatibility.

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

BiJoe — Thu, 07 Mar 2024 09:05:32 GMT

Hi, ticket number 2403040050003670

I understand regarding compatibility, then all the warehouse's imported table CREATE TABLE AS statements will generate build errors.

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

Anonymous — Fri, 08 Mar 2024 12:18:54 GMT

Hi BiJoe
Are you still facing this issue or did you get any resolution? Please let me know if you have any further questions. Glad to help.

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

BiJoe — Fri, 08 Mar 2024 12:21:50 GMT

No resolution yet, I have been contacted by both support tech and operations manager warehousing.

Re: [Database Projects] Statement 'CREATE TABLE' is not supported

BiJoe — Fri, 22 Mar 2024 14:31:52 GMT

This case seems to be stuck in support, not passing "Escalation engineers"