SQL Server ODBC writes data >1020 characters as varchar(max)

clmc9601

Hi Alteryx Community,

When I write wide columns of data to SQL Server via ODBC, Alteryx converts anything greater than 1020 characters to varchar(max) instead of varchar(1021). Column sizes are important in my database, and the write process becomes SO slow when writing varchar(max). One workflow writing <100 mb of data took 7 hours to run, but when I forced the columns to be <1000 characters, it ran in 40 minutes. I usually don't have the flexibility to just ignore columns >1000 characters. I read on the community that the cutoff is supposed to be around 8000 characters.

Does anyone know why this might be happening and/or how I can prevent it?

My workaround for the resulting column size is to use PostSQL to change the column type back to the exact size, but my workflows are still writing data so. slowly.

This is my ODBC connection: odbc:DRIVER={ODBC Driver 17 for SQL Server}

I generated test columns to see where the cutoff was. It's around 1020-1025 characters, as seen in these screenshots. I attached my workflow to generate the sample columns.

Thank you! I really appreciate your time and insight.

VarcharMaxExample.yxzp

BrandonB

Have you thought about using Bulk load instead of ODBC? This is significantly faster than a traditional ODBC connection. It doesn't address your column sizes question directly, but you may want to give it a try to see if it makes a big difference.