Skip to main content
Solved

SQLExecutor with Azure Synapse SQL

  • February 15, 2024
  • 2 replies
  • 231 views
kai_fme
Contributor
Forum|alt.badge.img+2

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!
 

Best answer by virtualcitymatt

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?

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

2 replies

virtualcitymatt
Celebrity
Forum|alt.badge.img+47
  • virtualcitymattCelebrity
  • Celebrity
  • Best Answer
  • February 15, 2024

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?

kai_fme
kai_fme
Contributor
Forum|alt.badge.img+2
  • kai_fmeContributor
  • Author
  • Contributor
  • February 15, 2024

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.

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.