We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).

When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.

However, I am updating another record to have the same photo and when I update/insert the existing data, an extra leading 0 seems to be applied to the data thus breaking the decoding of the image.

When the image is inserted by the application, the binary data is correct and starts with: 0xFFD8

When I copy that binary data, and use an update/insert statement to apply this photo to another record, the data changes and starts: 0x0FFD

This means that the data cannot be converted back to an image.

Google has let me down on this occasion, though it may be that I'm not using the correct search terms.

Any assistance greatly appreciated, and if I can add any more info to assist you, in assisting me, please let me know and I'll add whatever I can.

Link to a pastebin showing data before/after insert/update: http://pastebin.com/7jrtivZ8

  • 1
    Show how exactly you are updating/inseting the same photo. – HingeSight Jun 12 '15 at 10:59
  • 2
    Your binary representation has an odd number of digits. It takes two hex digits to specify a single byte, hence the padding. Are you really trying to store half a byte? That would seem to be an odd (ha!) thing to do. e.g. DECLARE @v varbinary(max) = 0x123; SELECT @v; – Paul White says GoFundMonica Jun 12 '15 at 11:08
  • This is the update statement: pastebin.com/w38ySEqf – OGarvey Jun 12 '15 at 11:15
  • 1
    The image data you are inserting is 65535 character long, including the leading 0x. Now 65535 = 2^16 - 1. Please, check if somewhere the code or approach you are using to generate the update statement is not limiting the length of the data. – HingeSight Jun 12 '15 at 11:36

The data you pasted is exactly 65533 hex characters. Including the "0x" prefix, this is 65535 which is exactly 2^16 - 1. This strongly hints that the code that generates the hexadecimal string representation of the binary data is limited to 65535 characters. Very likely, it is using a buffer of that length.

If you are using SQL Server Management Studio to retrieve a varbinary value to use in your update query, please note that the value will be truncated according to this SSMS setting:

Tools / Options / Query Results / Results to Grid / Max Characters Retrieved / Non XML Data

This setting happens to have a maximum value of 65535. If you set this max value to, say, 31, you would get the same odd-numbered string from your example:

select * from photo -- 0xFFD8FFE000104A46494600010101 -- 29-digit hex string

This StackOverflow answer explains that the when there is an odd number of hex characters, SQL server will assume there is a leading 0. So if you write this:

update Photo set photo_data = 0xF -- a 1-digit hex string

It is functionally equivalent to writing this:

update Photo set photo_data = 0x0F

And in fact selecting it out of the table will show you it is 0x0F.


I've managed to resolve the issue in a round about way.

Rather than inserting a new record with the data copied from an existing photo, I have created a dummy user, which I can add the photo to, and then update the id of the record to link to the correct user.

  • That's a hack which may bite you back at some point. They usually do :-) – Mariusz Sep 28 '15 at 15:51
  • 1
    Oh indeed it is and were it to be something that we'd use going forward it definitely wouldn't be acceptable to me, but fortunately it's for a soon to be replaced web application where only one customer actually uses this functionality and won't need to do this again before the replacement is released. – OGarvey Sep 30 '15 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.