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

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.

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.

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.

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.

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:

This is internally tracked as FMEENGINE-73455. 

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

-Andrea

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.

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.

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

@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!

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

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.