1

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?

Ronaldinho Learn Coding
  • 13,254
  • 24
  • 83
  • 110

1 Answers1

2

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

S3S
  • 24,809
  • 5
  • 26
  • 45
  • same problem when I used your query above with my value. Here is the same problem but I dont understand the solution, can you help http://stackoverflow.com/questions/29913283 – Ronaldinho Learn Coding Aug 29 '16 at 20:21
  • can you paste your value? If not, does it also happen when you SET ANSI_PADDING OFF – S3S Aug 29 '16 at 20:43
  • @RonaldinhoLearnCoding added an edit. Haven't figured out how to prevent it, but figured out WHY – S3S Aug 29 '16 at 21:20