Skip to main content
  • EN

Hi,

we connected from FME Desktop 2022.2.5 successfully to a Azure Synapse SQL Pool using the "Microsoft Azure SQL Database (JDBC)" connection. 
I can use the Reader without any problems and query data from the tables. However I need to use SQLExecutor Transformer as the queries need to be dynamically created. But this gives me the following error:

com.microsoft.sqlserver.jdbc.SQLServerException:  '@@TRANCOUNT' is not supported.

Seems like the SQLExecutor performs additional statements in the background which are not supported by Synapse. 

Are there any workarounds to avoid this issue or to force SQLExecutor not to use additional statements?

Thx for your help!
 

I’m not sure if this helps at all but it looks like FME 2023.1 has the format available if you’re available to upgrade this might be the go

 


another option could be to try and add the reader as a resource - it could be the reader has some other hidden settings which for sone reason the SQLExcecutor is missing.

another thing to try is to swap out the SQLExecutor  for a FeatureReader and try and leverage the SQL to execute before read. If this works maybe you could create a temp view/table or something to read from?

Hi vrtualcitymatt,

thanks for your answer.

The new format in 2023 might be really an option. Unfortunately update will be only available in 2 months for me.

Until then I think I need to really use the workaround with a temporary table or file as you proposed. The reader as resource is not really an option as I need to query first data from another DB and use these results for the query on the Synapse SQL.

Reply


Community Stats

30,108
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.