CODEWITHSUNDEEP
sqlsql-serverbase64
Kevin M
Kevin M

Reputation: 199

String or binary data would be truncated saving base64 images

Respectfully, please don't respond to this by telling me my field length is not long enough. This is not the case. And Respectfully, please don't respond to this by telling me that sp_executesql cannot accept input longer than 2K. This also is not the case.

My Problem: I'm submitting a query using sp_executesql where I'm stuffing base64 encoded images into an nvarchar(max) field. In some cases, I get the following error:

string or binary data would be truncated. The statement has been terminated.

I have examples where the text (which includes multiple base64 encoded images) exceeds 5 megs. That also pushes the sp_executesql length beyond 5 megs. Sometimes these work. And I've got examples where I've included very small base64 encoded images. Again, sometimes they work.

My working assumption: It seems sp_executesql is encountering some sequence of characters in the base64 encoded text which is causing SQL server to throw the "would be truncated" error.

My Question: Is there some method of coding my text to prevent SQL from blowing up when I issue my insert/update statement?

Limitations: I'm using an open source CRM, so I'd like to keep from replacing the insert/update procedures altogether. But I'm OK with changing the way I encode data that is sent through the existing procedures.

Any help would be greatly appreciated. I'm stumped!

Upvotes: 1

Views: 2382

Answers (1)

Kevin M
Kevin M

Reputation: 199

My Solution: I changed nvarchar(4000) to nvarchar(max) in my audit table which was updated by a trigger.

Details: I neglected to check for triggers in my open source CRM. Whenever an update was submitted to MY_TABLE, a trigger created an entry in MY_TABLE_AUDIT.

The field throwing the error was defined as nvarchar(max) in MY_TABLE and nvarchar(4000) in MY_TABLE_AUDIT. When I changed nvarchar(4000) to nvarchar(max), the trigger worked and I was able to save my images.

So how did the 5 meg text field get into the database? It must have happened on an initial insert which did not fire the trigger or when importing historical data with the trigger disabled.

Upvotes: 1

Related Questions