Synapse SQL drop table within a transaction

Question

Hi all,
I am trying to create a transaction in Synapse that I run a stored proc within with the ability to rollback but I keep getting this error when i exec the sp:

Msg 111213, Level 16, State 1, Line 1
111213;The current transaction has aborted, and any pending changes have been rolled back. Cause: 110806;A distributed query failed: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

The stored proc its trying to execute the following e.g query:

if object_id('[tablea]') is not null
drop table [tablea]

    select 
                 customer
                ,number
                ,invc



    into [tablea]
    from 
    (select * from tableb)

Answer 1

Hello @Rock, Danny ,
Thanks for the ask and also for using the forum .

This is what I tried , created a proc which just checks if a table exists and if it does it drops it .

CREATE PROC Testproc  
AS   
IF OBJECT_ID('[SINK]') IS NOT NULL  
DROP TABLE SINK  

BEGIN TRAN   
	EXEC TESTPROC  
ROLLBACK TRAN   

I am getting the below error .

Msg 111212, Level 16, State 1, Line 21
111212;Operation cannot be performed within a transaction.

This may be related to this .
"No support for DDL such as CREATE TABLE inside a user-defined transaction" .

Let me know if this helps .

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.