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?
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.