0

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?

DiegoM
  • 83
  • 1
  • 7

2 Answers2

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.

Screenshot that shows the Azure Synapse Analytics connector.

KarthikBhyresh-MT
  • 4,560
  • 2
  • 5
  • 12
0

So after running some tests around this issue, it seems like Mapping Dataflows do not like Temp Tables when I call my stored procedure.

The way I ended up fixing this was that instead of using a Temp Table, I ended up using a CTE, which believe it or not, runs a bit faster than when I used the Temp Table.

@KarthikBhyresh

I looked at that article before, but it wasn't an issue with the sink, I was using Synapse LS as my source and a Data Lake Storage as my sink, so I knew from the beginning that this did not apply to my issue, even though it was the same error number.

DiegoM
  • 83
  • 1
  • 7