Alteryx Output Error with Microsoft Azure SQL Synapse

[Ir3n3_-_M4n]

I'm trying to write a table on Microsoft Azure SQL Synapse with Alteryx version 2020.2.2.27029.

The table "D365.Prova" already exists in Synapse and the user I'm using has the following permissions on the db: db_owner, db_datawriter and db_datareader. I'm using the ODBC connection with "Overwrite Table (Drop)" option, but I'm getting the following error

Output Data (42)
Error creating table "D365.Prova": [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111212;Operation cannot be performed within a transaction.
CREATE TABLE "D365"."Prova" ("COL1" int,"COL2" varchar(10),"COL3" varchar(20),"COL4" varchar(20),"COL5" float,"COL6" varchar(50))"

If I use the "Append existing" option, no error is returned. If I use this option with the Pre Create SQL statement "TRUNCATE TABLE D365.Prova" I get the same above error. It seems that TRUNCATE and DROP give problems, while INSERT statement no.

Do you have any hint to solve the problem? I searched around but couldn't find any solution. Please tell me if you need any other info.

[Ir3n3_-_M4n · Accepted solution]

DDL statements cannot be wrapped in a transaction in Azure Synapse as noted in the limitations.

With a DELETE FROM in the Pre Create SQL statement it works just fine.