0

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.

Sigurd Felix
  • 1
  • 2
  • I tried some more stuff: Converting to Nvarchar and then to Varbinary Result: the bytesize is extremely small but the varbinary determines the encoding to UTF-16 LE Converting to Varbinary and then to Nvarchar Result: The image field still doesn't accpet Nvarchar input Added a collation COLLATE Latin1_General_100_CI_AI_SC_UTF8 Result: error message, this is only available in SQL 2019. That could be a last resort. – Sigurd Felix Jan 07 '23 at 13:47
  • Chat GPT also gave me the suggestion to use Nvarchar – Sigurd Felix Jan 07 '23 at 13:48
  • Chat GPT also came up with: convert(varbinary(max),@string,1 ) Result: Error converting data type nvarchar to varbinary. – Sigurd Felix Jan 07 '23 at 14:40
  • I fixed this issue with a workaround. I installed a SQL Express Server 2019 and linked it to the other SQL instance. Then I made a table with the same fieldtype image and used this syntax: `convert(varchar(max), COLLATE Latin1_General_100_CI_AI_SC_UTF8)` Then I scripted the image field value from the SQL 2019 table imagefield to the other database and it worked! – Sigurd Felix Jan 09 '23 at 09:42

0 Answers0