We are trying to import data into Paxata from Azure SQL Data Warehouse/Synapse Analytics using a query that involves temp tables. Our network person set up a custom connector for this data source using the JDBC connector, as the default Azure SQL DW connector requires a data lake reference, which we don't use. I'm fuzzy on the details since it isn't my area.
When trying to import data using this query and data source, Paxata shows a SQL error: "Could not request data: 111212;Operation cannot be performed within a transaction." We were able to import data from a different query that used CTEs rather than temp tables, so we narrowed the issue down to the creation of temp tables. The CTEs are too slow for use in the original query though.
We also confirmed that the user ID being used in the connector has the correct permissions to run the query, so this isn't the issue either. Is there something we need to change in the connector or other settings to allow us to use temp tables? Thanks in advance!