Skip to main content
Question

FME Writer Hangs when connecting to SQL Server 2019


We have been using FME 2021.2 ( build 21789) and recently upgraded our SQL database to 2019. We started seeing timeout errors when the FME writer tries to connect to the SQL Server and keeps saying 

2023-10-16 15:04:58|  2.2| 0.0|INFORM|Microsoft SQL Server Non-Spatial Writer: Trying to connect using Microsoft OLE DB Driver for SQL Server...

We use windows scheduled task manager to run our jobs and this is causing some of the jobs block other FME jobs.

 

Any ideas on what may be the issue.

 

Thanks,

AD

14 replies

hkingsbury
Celebrity
Forum|alt.badge.img+55
  • Celebrity
  • October 17, 2023

Following this as we occasionally have similar issues but can't reliably replicate it


samg
Participant
Forum|alt.badge.img
  • Participant
  • October 17, 2023

I'm a coworker.  To add some additional context, we see a query open but sleeping on the SQL Server like this:

SELECT name, scale FROM sys.columns WHERE object_id = OBJECT_ID('MySchema.[MyTable]'AND user_type_id = TYPE_ID('xml')

 

This creates a lock on the database schema, preventing many other FME jobs from running if they need a schema lock to truncate or drop/recreate a table.  We found that changing the FME transformer from drop/create to truncate fixes the issue, but we have this design pattern in many different workspaces, so we are trying to find a fix without updating many transformers. 


kailinatsafe
Safer
Forum|alt.badge.img+21
samg wrote:

I'm a coworker.  To add some additional context, we see a query open but sleeping on the SQL Server like this:

SELECT name, scale FROM sys.columns WHERE object_id = OBJECT_ID('MySchema.[MyTable]'AND user_type_id = TYPE_ID('xml')

 

This creates a lock on the database schema, preventing many other FME jobs from running if they need a schema lock to truncate or drop/recreate a table.  We found that changing the FME transformer from drop/create to truncate fixes the issue, but we have this design pattern in many different workspaces, so we are trying to find a fix without updating many transformers. 

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs? 

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.


  • Author
  • October 19, 2023
kailinatsafe wrote:

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs?

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.

Yes, when the table handling is set to truncate, the job seems to move ahead. We tested one of the jobs using latest build and did not have any issue but it was a smaller test job. We did also install the latest OLE DB 19 driver and still saw the issues.

 

We saw some different warnings last night and wasnt sure if it is related -

 

 

2023-10-19 07:46:28|  1.0| 0.0|INFORM|Microsoft SQL Server Non-Spatial Writer: Trying to connect using Microsoft OLE DB Driver for SQL Server...

2023-10-19 07:47:12|  1.0| 0.0|WARN |    MS SQL Server: Connection not properly cleaned up, please visit http://www.safe.com/support. Connection key 'xxxx_|_Xxxxxxx_|_xxxx_user_|_xxxx*_|_1000_|_ADODB_R'

 

 

2023-10-19 06:30:04|  1.0| 0.6|WARN |Microsoft SQL Server Non-Spatial Reader: Failed to connect using Microsoft OLE DB Driver for SQL Server. Provider error '(-2146824582) Provider cannot be found. It may not be properly installed.'. Connection string 'Provider=MSOLEDBSQL;DataTypeCompatibility=80;Data Source=XXXX;Initial Catalog=XXXDB;User ID=XXXUser;Password=XXXX'

2023-10-19 06:30:04|  1.0| 0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using SQL Server 2012 Native Client...

 

 

 

 


kailinatsafe
Safer
Forum|alt.badge.img+21
kailinatsafe wrote:

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs?

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.

Hello @adave​, can you confirm the OLE driver was actually installed? We have seen instances where FME does not install this, for some reason. Based on the new WARN's, sounds like FME can't connect / find the driver.


  • Author
  • October 23, 2023
kailinatsafe wrote:

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs?

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.

Hi kailinatsafe,

 

We noticed that although we did install the correct version of OLE DB 2019, we are still having issues in FME 2021 recognizing this version .

We did note on a different post on the community that support for MS OLE DB Driver v19 (MSOLEDBSQL19) was going to be made available on FME 2023 

https://community.safe.com/s/question/0D54Q00009Vq2HkSAJ/new-fme-server-build-windows-which-odbc-driver-to-use

 

In addition, my Colleague researched the issue and noted -

FME 2021 is likely trying to invoke msoledbsql.dll, and the new version is msoledbsql19.dll

https://learn.microsoft.com/en-us/sql/connect/oledb/applications/components-of-oledb-driver-for-sql-server?view=sql-server-ver16

 Based on the above articles, we feel that we need to manually point the SQL Connection String to be “Provider=MSOLEDBSQL19” or we need a hotfix to the 2021 version of the FME_ADO plugin that facilitates that for us.

We were wondering if there is a way that Safe can help in getting FME 2021 to invoke MSOLEDBSQL19 manually or provide a fix that can be used?

 

Please advise?

 

Thanks

AD

 


kailinatsafe
Safer
Forum|alt.badge.img+21
kailinatsafe wrote:

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs?

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.

Hello @adave], thanks for getting back to me! Great find! Are you able to use this online form to submit a support case with Safe Software? This way, one of our database specialists can assist you with this directly. Happy to help, Kailin.


  • Author
  • October 24, 2023
kailinatsafe wrote:

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs?

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.

Hi kailinatsafe,

 

I had created a case (C694350) but I was got more response on this thread.

I can add all of this thread info on it if that helps.

 


kailinatsafe
Safer
Forum|alt.badge.img+21
kailinatsafe wrote:

Hello there @samg (City of Mesa)​ and @adave (City of Mesa)​, thanks for information! Sorry to hear you're encountering this issue. Were there any other changes made besides upgrading to SQL 2019?

 

Just to clarify, do you mean when the table handling is set to truncate on the writer, the hang does not occur? Are you able to install and test the latest official build and confirm the writer still hangs?

 

Also, if it is hanging in drop and create mode, it might be helpful to share a logfile to help determine the action the writer is having issues with (eg. the dropping or creating). Happy to help, Kailin.

Hello @adave​, I'll take a look, thanks for letting me know! Kailin.


nordpil
Supporter
Forum|alt.badge.img+11
  • Supporter
  • June 5, 2025
adave wrote:

2023-10-19 07:46:28|  1.0| 0.0|INFORM|Microsoft SQL Server Non-Spatial Writer: Trying to connect using Microsoft OLE DB Driver for SQL Server...

2023-10-19 07:47:12|  1.0| 0.0|WARN |    MS SQL Server: Connection not properly cleaned up, please visit http://www.safe.com/support. Connection key 'xxxx_|_Xxxxxxx_|_xxxx_user_|_xxxx*_|_1000_|_ADODB_R'

Do you get the password in clear text in those log lines? I am trying to reproduce (to file a bug to Safe) for a an issue where I get this warning message and the password is leaked into the log file...

 


nordpil
Supporter
Forum|alt.badge.img+11
  • Supporter
  • June 5, 2025

I have seen this (blue boxes is the password, orange boxes is the server hostname). The password shouldn’t be there, the hostname is fine for the logs, but I don’t want it visible on the Internet… FME Server 2022.2.8 and MSSQL 2022
 

 


takinselure
Safer
Forum|alt.badge.img+2

Hi Nordpil,

Could you please provide more information on how old the workspace is that’s causing this issue? From what you’ve described, it appears to be related to an older bug resolved in more recent versions of FME.

We recommend upgrading to the latest version of FME Flow, as it may help address the issue.

Thank you,
Toyosi A


steveatsafe
Safer
Forum|alt.badge.img+12

@nordpil,

Another possible tack would be to ensure the Reader/Writer/FeatureReader/FeatureWriter is updated to the version of FME Workbench you are using… we believe this issue was fixed in FME 2022.2, but could still be a problem in workspaces created in older versions of FME and not fully updated in the fixed versions.  If this continues, perhaps open a ticket with Safe Software Support so we can investigate further.


nordpil
Supporter
Forum|alt.badge.img+11
  • Supporter
  • June 13, 2025

I have submitted a workspace to reproduce the issue to our reseller. It is a bit fishy, I can provoke the workspace to fail, but the message is in the logs doesn’t show at every case.

How to provoke it - write data with giant blobs using a MSSQL spatial writer (probably works the same with non-spatial?) and set features per transaction to very high, the amount of data needed to reproduce probably depends on the server performance/memory, but I can get this workspace to fail every time, and in approx one out of 10 cases it will leak the p/w in the log.


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