Sigurd Felix
Sigurd Felix

Reputation: 1

When I write a string to an image field in SQL it gets saved in UTF-16 LE encoding and I need it to be UTF-8

I have about 1200 databases in SQL 2016 Enterprise in which documents are stored as BLOB's in image fields in the database. I migrated all the documents to our Document Management System and now I want to replace the files in the database with shortcuts to the corresponding files in the DMS. I tried it a few months ago and that went well. Now I run into an issue. When I use this command

convert(varbinary(max), <string value of shortcut>)

It get's written to the field. When I try to open the shortcut I get an error. If I create the same shortcut from our DMS it is exactly the same except for the encoding. My binary = UTF-16 little endian and the DMS shortcut is encoded in UTF-8. The filesize has also doubled, which is logical since UTF-16 uses two bytes for every character. When I change the encoding in Notepad++ my shortcut works.

I need my blob to be encoded in UTF-8. That is possible, I can upload a shortcut to the system that the database uses and then it's stored correctly. I can't change the collation of the table or field because this is a vendor database. It's a pretty old-fashioned system. Who uses Blob's in the first place and if you do, why image and not varbinary?

I'm not much of a programmer so any help would be greatly appreciated.

I tried updating the database to the latest client version (not SQL just the application). That didn't change anything. I tried nvarchar but that doesn't work on image fields.

Upvotes: 0

Views: 237

Answers (0)

Related Questions