Conversion error when writing binary data to SQL database

[Adriankp]

Hello,

I'm currently in a position where I have to write a ZIP file to an SQL database table from Alteryx, but I'm running into some issues with larger files. The process currently look like this:

1.) Read ZIP file as blob with Blob Input

2.) Convert blob to HEX encoded binary

3.) Output binary to SQL varbinary(max) using ODBC connection in Output tool

The issue I'm facing however is the below conversion error from the output tool that I cannot make sense of. It seems to happen as soon as the ZIP file is larger than just a couple MB, and results in data being missing in the ZIP in the database.

The ZIP file I've read as a blob and converted is approx. 14,5MB in size and the limit of the varbinary(max) data type should be approx. 2GB, so I'm not sure what the 58835888 or 16777214 is actually referring to here?

Has anyone encountered this conversion error and can actually make sense of it?

Thanks in advance.

[Gaurav_Dhama_]

Commenting to stay updated on this.

[apathetichell]

what's the database that you are using? what's the driver? did you create this table or are you updating it? basically something is trying to limit the size of the strings stored in the db. is it Alteryx? maybe. is it the driver? maybe. Is it the underlying table --- and how it's configured? maybe? these are basically the things I'd look at to troubleshoot this.

[Adriankp]

Issue seems to be occuring on at least both mssql 2017 & 2022, using ODBC Driver 17 in Alteryx. The table already exists, and Alteryx is just appending to it. I've tried writing the exact same file to the table through C#, and it works without any issues.

I guess I can try ODBC 18 to see what the results are.

[apathetichell]

Hey --- during your tests with C# --- are you inputting the same length string to your column? I'd recommend turning on logging at your driver level and checking where the error is actually occurring (Alteryx, the DB or the driver) and seeing what is sent to the DB. driver level logging is controlled by your ODBC 64 settings. 

I'm also curious if you can write to a net-new table vs the existing table. basically if the table has a set character length for that field--- and you are exceeding it --- I would 100% expect that error --- and the fix would be to increase the column size in your db.

[dreldrel]

Is it possible to use another format like JSON instead of ZIP files to upload your data?

[apathetichell]

@dreldrel binary should be smaller than json.

[ajinaniyan123]

It looks like the issue is coming from how Alteryx handles the data rather than from SQL Server itself. The error message about field width: 16777214 suggests that Alteryx is trying to treat the HEX string as a text field, which quickly hits a size limit once your ZIP file is larger than a few MB. Since varbinary(max) in SQL Server can handle files up to 2GB, the problem isn’t with SQL but with the way the data is being sent through the ODBC driver. To fix this, avoid converting the blob into HEX and instead write the blob directly to the varbinary(max) column, which removes the unnecessary string expansion. Also make sure you’re using the latest Microsoft ODBC Driver for SQL Server, because older drivers sometimes enforce size limits during inserts. For very large files, streaming or chunking the data is more reliable than trying to push it in one go.

[Adriankp]

I tried enabling ODBC tracing and running the workflow, but the logs aren't exactly intuitive. Not quite sure what I'm looking for, but not seeing anything fail?

I tried having Alteryx create a new table instead of appending to the existing one, and just writing the blob directly without converting it to hex encoded binary and it works. It does however set the datatype in the table to image instead of varbinary. After some more testing it seems that everything works and no truncation occurs as long as you use the image data type instead of varbinary. Both writing the blob directly and the hex encoded binary string. 

I wonder if this is a limitation in the output tool in Alteryx? The Image datatype is deprecated as far as I'm aware, so I'm not sure why this is what Alteryx still uses when creating a table..

[Adriankp]

Writing a blob directly to a varbinary(max) column gives me a hard error in Alteryx. Doesn't work at all. Alteryx does seem to be able to write the blob to the Image data type however, which is frustrating.