Skip to main content
  • EN

In the workspace I have a geometry extractor transformer, which outputs OGC Well Known Binary format.

After geometry extractor, SQL server non spatial writer follows. In the writer, the WKB field is defined as the varbinary (width 8000) type.

In an SQL server database, this field is defined as a varbinary (MAX) data type.

All translations went smoothly in the latest FME Workbench 2021 version.

However, in version 2022, it does not work. I tried different data type combinations for sql server writer and for database, however without success.

 

1. case

writer data type: database data type:

varbinary (8000) varbinary(MAX)

 

Microsoft SQL Server Non-Spatial Writer: Attribute 'abc' of feature type 'xyz' has specified type 'varbinary(8000)', but existing database table field has type 'varbinary_max'. Information may be lost during conversion.

Microsoft SQL Server Non-Spatial Writer: Attribute data '\\x01\\x03\\x00\\x00\\x0\\x95\\xAC!AR\\xB8\\x1EU\\ ... ' too large to fit in specified data type for column.

No data is written.

 

2. case

writer data type: database data type:

varbinary_max varbinary(MAX)

 

Microsoft SQL Server Non-Spatial Writer: Attribute 'abc' of feature type 'xyz' has specified type `varbinary(max)', but existing database table field has type `varbinary_max'. Information may be lost during conversion.

Microsoft SQL Server Non-Spatial Writer: Attribute data '\\x01\\x03\\x00\\x00\\x00\\x01\\x00\\x00\\x00$\\ ...' too large to fit in specified data type for column.

No data is written.

 

3. case

writer data type: database data type:

varbinary (8000) varbinary(8000)

 

Microsoft SQL Server Non-Spatial Writer: Attribute data '\\x01\\x03\\x00\\x00\\x00\\x01\\x00\\x00\\x00$\\ ...' too large to fit in specified data type for column.

Only enough small data is written.

 

4. case

writer data type: database data type:

varbinary_max varbinary(8000)

Microsoft SQL Server Non-Spatial Writer: Attribute data '\\x01\\x03\\x00\\x00\\x00\\x01\\x00\\x00\\x00$\\ ...' too large to fit in specified data type for column.

Only enough small data is written.

 

I do not have a license to use SQL Server spatial data writer, so I do the conversion to WKB and write it to the SQL server database.

Therefore, in short, how to write WKB data to sql server table, with FME Workbench 2022.

 

Thanks;

Hi @rzrnc​, I'm sorry you ran into this issue. Thanks for the detailed description which help me reproduce the issue in FME 2022. I've file a ticket for our development team (FMEENGINE-73455), and we'll try to keep you posted via this thread. Apologies again for any inconvenience.

Thank you for your effort, and I believe that you will solve the problem successfully and quickly.

Best regards.

Hi @rzrnc​, I'm sorry you ran into this issue. Thanks for the detailed description which help me reproduce the issue in FME 2022. I've file a ticket for our development team (FMEENGINE-73455), and we'll try to keep you posted via this thread. Apologies again for any inconvenience.

Hi;

After 2 updates, the problem with the varbinary_max data type still exists. In relation to the previous case, if the Microsoft SQL Server Non-Spatial JDBC Writer is used, the varbinary_max data type is recognized correctly. However, a new problem has occurred for both Microsoft SQL Server Non-Spatial JDBC Writer and Microsoft SQL Server Non-Spatial Writer. Translation log reports alerts in the sense:

For simple data types like char(x) or varchar(x):

 

Microsoft SQL Server Non-Spatial Writer: Attribute `atr1' of feature type `type1' has specified type `char(5)', but existing database table field has type `char(10)'. Information may be lost during conversion

Microsoft SQL Server Non-Spatial Writer: Attribute `atr2' of feature type `type2' has specified type `char(6)', but existing database table field has type `char(12)'. Information may be lost during conversion

Microsoft SQL Server Non-Spatial Writer: Attribute `atr3' of feature type `type3' has specified type `varchar(4)', but existing database table field has type `varchar(8)'. Information may be lost during conversion

Microsoft SQL Server Non-Spatial Writer: Attribute `atr4' of feature type `type4' has specified type `char(1)', but existing database table field has type `char(2)'. Information may be lost during conversion

 

All data types in the writer and in the database are identically defined, however, FME recognizes those in the database as being twice as large and issues warnings.

 

Thank you.

Hello @nampreetatsafe​ ,

 

I am seeing this same issue with FME Desktop 2022.1 when writing to fields that are varchar(max) in the database.

 

I have not tested with any other field data types, but I was able to run the workbench from a computer that still has FME Desktop 2021.1 on it, and there was no issue.

 

Thank you,

Brian

Hello @nampreetatsafe​ ,

 

I am seeing this same issue with FME Desktop 2022.1 when writing to fields that are varchar(max) in the database.

 

I have not tested with any other field data types, but I was able to run the workbench from a computer that still has FME Desktop 2021.1 on it, and there was no issue.

 

Thank you,

Brian

Hi;

With varbinary_max datatype​,error is connected with bulk insertion. If you disable bulk insertion, error is gone.

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.

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.

Hi;

Thanks for the information and troubleshooting.

Best regards.

Reply


Community Stats

30,126
Posts
114,365
Replies
37,763
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.