sql-serverazure-synapsealteryx

Alteryx Output Error with Microsoft Azure SQL Database

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

The table "D365.Prova" already exists in the db 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.

Solution

  • 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))"

    The above error message states that

    The use of DDL statements like CREATE TABLE is not allowed within a user-defined transaction in Azure Synapse dedicated pool.

    Here are few Limitations suggested by @Thom A

    As you have mentioned that you want to use DROP command.

    I have tried the below approach as workaround:

    enter image description here

    You can use the Syntax for Azure synapse dedicated pool

    DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name   
[;]

    OR

    if  object_id ('ddpool.D365.Prova','U') is  not  null  drop  table ddpool.D365.Prova ;

    OR

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ddpool].[Prova]') AND type in (N'U'))
DROP TABLE [ddpool].[Prova]