ADF Mapping Dataflow Temp Table issue inside SP call

Question

I have a mapping dataflow inside a foreach activity which I'm using to copy several tables into ADLS; in the dataflow source activity, I call a stored procedure from my synapse environment. In the SP, I have a small temp table which I create to store some values which I will later use for processing a query.

When I run the pipeline, I get an error on the mapping dataflow; "SQLServerException: 111212: Operation cannot be performed within a transaction." If I remove the temp table, and just do a simple select * from a small table, it returns the data fine; it's only after I bring back the temp table that I get an issue.

Have you guys ever seen this before, and is there a way around this?

Answer 1

If you go through the official MS docs, this error is very well documented.

Failed with an error: "SQLServerException: 111212; Operation cannot be performed within a transaction."

Symptoms

When you use the Azure SQL Database as a sink in the data flow to preview data, debug/trigger run and do other activities, you may find your job fails with following error message:

{"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'sink': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: 111212;Operation cannot be performed within a transaction.","Details":"at Sink 'sink': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: 111212;Operation cannot be performed within a transaction."}

Cause

The error "111212;Operation cannot be performed within a transaction." only occurs in the Synapse dedicated SQL pool. But you mistakenly use the Azure SQL Database as the connector instead.

Recommendation

Confirm if your SQL Database is a Synapse dedicated SQL pool. If so, use Azure Synapse Analytics as a connector shown in the picture below.