Following this as we occasionally have similar issues but can't reliably replicate it
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.tMyTable]') 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.
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.tMyTable]') 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.
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...
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.
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
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.
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.
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.
