• Home
  • Forums
  • FME Form
  • Transformers
  • FME inserts NULL value into NVARCHAR(MAX) columns in SQL Server using Microsoft SQL Server Non-Spatial format after the latest FME upgrade
Solved

FME inserts NULL value into NVARCHAR(MAX) columns in SQL Server using Microsoft SQL Server Non-Spatial format after the latest FME upgrade

  • 19 September 2022
  • 14 replies
  • 149 views
david_
Rank
Badge +4

All our processes that were inserting NVARCHAR(MAX) values into SQL Server databases have stopped working correctly (FME inserts NULL values into NVARCHAR(MAX))after the latest FME upgrade (2022).

 

We have found a temporary solution using the Microsoft SQL Server Non-Spatial (JDBC) format instead of Microsoft SQL Server Non-Spatial but it requires to modify a lot of workspaces manually.

 

Does anyone know if this is a bug and if it will be fixed?

 

Thank you!

 

icon

Best answer by keitho 12 January 2024, 17:18

View original

14 replies

david_r
Rank
Userlevel 4

Depending on how old your previous FME version was, it could be a change in behavior, either in FME or in the SQL Server client drivers shipped with FME.

The question is not specific enough to say more, unfortunately.

david_
Rank
Badge +4

Hi @david_r​ , thank you for your answer and sorry if the question was not specific.

 

The issue is easy to demonstrate.

 

  1. Create a SQL Table with a nvarchar(MAX) column.
  2. Create a featureWritter or Writer for SQL Server using Microsoft SQL Server Non-Spatial.
  3. Set an attribute as nvarchar_max.
  4. Run the process.

 

As you can check, the value won't be inserted. Besides that, the log shows an incorrect warning.

In order to clarify you can follow the attached image.

 

issue_1 

 

Thank you!

david_r
Rank
Userlevel 4

Hi @david_r​ , thank you for your answer and sorry if the question was not specific.

 

The issue is easy to demonstrate.

 

  1. Create a SQL Table with a nvarchar(MAX) column.
  2. Create a featureWritter or Writer for SQL Server using Microsoft SQL Server Non-Spatial.
  3. Set an attribute as nvarchar_max.
  4. Run the process.

 

As you can check, the value won't be inserted. Besides that, the log shows an incorrect warning.

In order to clarify you can follow the attached image.

 

issue_1 

 

Thank you!

I just tested with the following setup, but without being able to reproduce the problem. The varchar_max value was written successfully.

  • Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64)
  • FME(R) 2022.1.1.0 (20220728 - Build 22623 - WIN64)

If the problem persists, you may want to escalate through your FME reseller.

david_
Rank
Badge +4

Thank you again @david_r​ . With varchar_max there is no problem.

 

The issue/bug is with nvarchar_max values as I explained.

 

Could you test with that? thanks in advance!

 

Regards.

david_r
Rank
Userlevel 4

Thank you again @david_r​ . With varchar_max there is no problem.

 

The issue/bug is with nvarchar_max values as I explained.

 

Could you test with that? thanks in advance!

 

Regards.

Sorry, didn't catch that one. I redid the test with nvarchar(max) and observed this:

  • If FME drops and recreates the table, the nvarchar is inserted properly
  • If FME appends to an existing table, the nvarchar field contains an empty string (not NULL). There is also the following warning in the log:
WARN  |Microsoft SQL Server Non-Spatial Writer: Attribute `test' of feature type `test_table' has specified type `nvarchar(max)', but existing database table field has type `nvarchar_max'.  Information may be lost during conversion
WARN  |Microsoft SQL Server Non-Spatial Writer: 1 attribute value(s) were truncated

I observe the same issue with nvarchar(4000) in the FME, so it does not seem specifically linked to varchar(max).

I recommend that you transfer the details to Safe support. Consider also linking to this thread.

A
Rank
Badge +10

Hi @david_​ ,

 

Thanks for raising a support ticket for this. 

 

We are aware of this issue where nvarchar(max)/varbinary(max) are not inserting values appropriately for existing tables. As @david_r​ , pointed out there is this warning that is logged:

Microsoft SQL Server Non-Spatial Writer: Attribute `geometry_wkb' of feature type `dbo.osf_pois_polygon_NS' has specified type `nvarchar(max)', but existing database table field has type `nvarchar_max'. Information may be lost during conversion

 

Aside from using the Microsoft SQL Server Non-Spatial (JDBC) format, another possible workaround would be to set "Bulk Inserts" to "No" when using the MSSQL_ADO format:

image 

This is internally tracked as FMEENGINE-73455. 

We will update this community post when it's been resolved.

-Andrea

nampreetatsafe
Rank
Userlevel 1
Badge +10

Update: A fix for this issue will be included in FME 2023. You can test it out in the current FME 2023 beta release (build 23223+) which is available for download here.

keitho
Rank

Having the same trouble (FME inserts NULL values into NVARCHAR(MAX)) after upgrading to FME Form 2023 build 23283.

A

Having the same trouble (FME inserts NULL values into NVARCHAR(MAX)) after upgrading to FME Form 2023 build 23283.

Yes - we're having this issue too after the latest updates - workspaces that previously worked are now inserting empty strings into nvarchar(max) SQL columns, with a truncation warning in the FME Flow job logs.

swatto
Rank

Same issue team. FME 2023 don't party with nvarchar_max.

natalieatsafe
Rank
Badge +6

@keitho​ @andrewbnz​ @swatto​ Hi there, I just wanted to provide you with an update on this issue. Digging into this a bit, it looks like there were two related but separate bugs occurring here. The newer bug, where FME Form 2023.0's MSSQL writers were inserting null values into nvarchar(max) fields, now has a solution in FME Form 2023.2 build 23706+. If you're able to, install and give FME Form 2023.2.1 a try and see if those nvarchar(max) values are better handled therein!

keitho
Rank

Thanks @natalieatsafe​, I can verify that 2023.2.1 has solved this issue for us.

natalieatsafe
Rank
Badge +6

Thanks @natalieatsafe​, I can verify that 2023.2.1 has solved this issue for us.

Excellent! Thanks for confirming @keitho​ , glad FME Form 2023.2.1 is doing the trick!

david_
Rank
Badge +4

Hi there, I am the author of this ticket.

Thanks for checking @keitho​ , could you please tell us what type of SQL Server connection you used in your tests?

Originally, the issue appeared when using non-spatial SQL connections. In our case, as I commented, we had to change all our workspaces to JDBC connections to "fix" the problem so I am interested to know which one you used.

 

Thanks also to @natalieatsafe​ for informing us.

Reply


Community Stats

28,723
Posts
109,510
Replies
36,559
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.