Skip to main content
Solved

SQLExecutor with Azure Synapse SQL

  • February 15, 2024
  • 2 replies
  • 160 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?

View original
Did this help you find an answer to your question?

2 replies

virtualcitymatt
Celebrity
Forum|alt.badge.img+35
  • 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
1 person upvoted this
  • kai_fme
    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.

Reply


Helpful Members This Week

Community Stats

31,139
Posts
118,062
Replies
38,758
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.