topic Re: Azure Synapse target: Error with long string value in Qlik Replicate

Azure Synapse target: Error with long string value

bilzekek — Wed, 10 Aug 2022 13:45:34 GMT

Hi,
I have a problem with Replicate, when transferring data from on-prem SQL server to Azure Synapse, dedicated SQL pool.
As an example, I have a simple task with 1 table and only two columns. There are no transformation on the table itself, and neither any global transformations on the task.

The initial load, however, fails with below error:

I have tried to use both table and global transformation, that take use of the substr(X,Y,Z).

So for example as a global transformation rule, I have used “replace column value” option and then the function: substr($AR_M_SOURCE_COLUMN_DATA,1,4000).

This removes the error, but also insert NULL in all rows of the [description] column.

What can I do to avoid this error and get the data transferred?

Re: Azure Synapse target: Error with long string value

KellyHobson — Wed, 10 Aug 2022 14:00:47 GMT

Hey @bilzekek

Can you try adding the following internal parameter:

Manage endpoint -> Synapse endpoint -> Advanced tab -> internal parameters

Search for "syntax" and set value to.

"syntax" = "AzurePDWMaxLob"

Then try the task without the global transformation.

Thanks,

Kelly

Re: Azure Synapse target: Error with long string value

bilzekek — Wed, 10 Aug 2022 16:42:28 GMT

Hi,

This does seem to do the trick, although it cuts the length of the string in the column to 4000. I dont suppose there is anything to do there?

Thanks

Re: Azure Synapse target: Error with long string value

lyka — Wed, 10 Aug 2022 16:50:49 GMT

Hello,

Are you still using substr($AR_M_SOURCE_COLUMN_DATA,1,4000)

If yes then that maybe the reason why its truncating the column to 4000

You can increase the change the limit lob size on your task settings

Thanks

Lyka

Re: Azure Synapse target: Error with long string value

bilzekek — Wed, 10 Aug 2022 17:55:16 GMT

Hi Lyka,

I removed the transformation as instructed.

Where do i change lob size limit? It says it is unsupported on the table settings: "Table-level LOB column handling is not supported with the Microsoft Azure Synapse Analytics target endpoint."

Re: Azure Synapse target: Error with long string value

lyka — Wed, 10 Aug 2022 17:58:58 GMT

Hello,

Go to task settings > target metadata

Thanks

Lyka

Re: Azure Synapse target: Error with long string value

bilzekek — Wed, 10 Aug 2022 18:01:05 GMT

Thank you, will try this.

Re: Azure Synapse target: Error with long string value

MoeE — Mon, 25 Nov 2024 22:25:07 GMT

Hi @KellyHobson,

The Synapse target limitations state that full lob replication is not supported. Is it okay to keep this setting on?

Regards,

Mohammed