Update varbinary(MAX) column

Question

I am trying to update a varbinary(MAX) column, it actually stores saved data of a Word file that an user uploaded from a website.

What happened was an user uploaded a wrong file so I need to update the column to reflect a correct file.

What I did was that in a testing machine I uploaded the correct file so it was saved to the database and I can see and copy the "varbinary(MAX) value" and use it to replace the wrong one

The value looks like: 0x504B03041400060008000........FBB9

I tried a straight forward UPDATE query:

UPDATE my_table Set datafile =  0x504B03041400060008000........FBB9

It says 1 row(s) affected, but that file can't be open after downloaded from the font-end web, I notice that saved value is 0x0504B03041400060008000........FBB (1 more extra 0 after 0x and the 9 at the end disappeared)

How do I do this?

Answer 1

The extra 0 is being added because the entire value you are setting it to is odd. SQL Server will pad it with an extra 0 to make it even.

declare @varmax varbinary(max) 
set @varmax = 0x1234567         --odd
select @varmax                  --returns 0x01234567 with the padded 0

set @varmax = 0x12345678        --even
select @varmax                  --returns 0x12345678

Your 9 is being dropped because you are entering an odd number of bytes that fills the max value. So, a 0 is inserted but this overflows the max number of bytes and thus is also truncated it seems. I was able to replicate your error... but don't know a way around it yet.

Here is the value i used for your test. Ignore the results... but you can copy them into your own SSMS and see the correct output.

http://rextester.com/LMGQ8686