Solving issues with nvarchar(max) values while replicating into SAP HANA

[Former Member]

I want to share some knowledge when it comes to issues (NULL value errors) while replicating values from a nvarchar(max) data type from MS SQL Server into a SAP HANA replicate database.

At some point in time we recognized at a customer project that a configured replication went down while the system has been already up and running for a few weeks. We investigated the log files and found an entry like "Cannot insert NULL or update to NULL: PROPERTYNAME".

This was rather unexpected because the source column, the column within the replication definition and the column inside the target SAP HANA database were marked with NOT NULL. The issue started to appear after new rows were inserted into the source database as the replication / subscription was already "VALID".

We started to figure out what causes the null-value inside the INSERT statement on the HANA side. The SAP support team suggested to change the data type of the column within the replication definition but that did not change anything. After that we tried to create the replication definition in an automated way by using rs_create_repdef to see if the result differs from our handmade definition, but it doesn't.

After a couple of days of research we figured out that there is a limitation within SAP HANA which causes this behaviour. A description within the official SAP Sybase replication server documentation pointed us to the root cause:

"To replicate LOB datatypes from the Microsoft SQL Server to SAP HANA replicate database, set the lr_partial_updates_allowed parameter to false"

We gave it a try, and it seems to work well.

Note: You can check your configuration setting within the replication agent by issuing the following command: ra_config lr_partial_updates_allowed