Solved

varchar(max) fields not coming through in ADLS Gen2

  • 22 October 2023
  • 5 replies
  • 86 views

Userlevel 1
Badge +1

I have a table with a varchar(max) field (stored in Dedicated Pool, which is a data source). and it won’t come through to ADLS Gen2 (ODX, version 6284.1). I’m using the Azure Synapse provider.

The longest field contains almost 4 million characters, but I’ve been unable to figure out whether this is a limitation in TX or Azure. Does anyone know?

icon

Best answer by Christian Hauggaard 13 November 2023, 11:22

View original

5 replies

Userlevel 6
Badge +7

Hi @Søren Sørensen ,

it may be a limitation - I do not know whether PolyBase is used in your scenario but having 1MB limitations on LOB data is fairly common in this field. A varchar(max) will store a pointer in the record (there are usually also limits in record size as well) to the actual data. Do you get an error or a timeout? I guess TimeXtender may be able to confirm.

Userlevel 1
Badge +1

Hi @rory.smith .

There are no error messages in the UI or logs because I load data “on demand”. But your question triggered something I should have thought of … to do a simple ODX load, which I just did … and ran into this one:

System.NullReferenceException: Object reference not set to an instance of an object

We might need @Christian Hauggaard or one of the other Community Managers to have a look at this.

Userlevel 6
Badge +5

@Søren Sørensen 

It may be better to add a ticket in the old system for this.

It may be about the size, but we will have to get a similar sized field to replicate the issue.

Userlevel 1
Badge +1

Thanks @Thomas Lind 

I’ll do that...!.

Userlevel 6
Badge +5

I replicated the steps described, creating a field varchar(max) on synapse with 4,000,000 characters


After which I received the following error message on the transfer task that "completed with warnings"

I managed to resolve the issue by increasing the connection timeout for the Azure DL storage on my ODX instance in the TimeXtender Portal

I then transferred the table to my MDW and confirmed that the field was brought over in SSMS

 

Reply