2

Recently I would like to do some data patching, and try to update a column of type varbinary(MAX), the update value is like this:

0xFFD8F...6DC0676

However, after update query run successfully, the value becomes:

0x0FFD8...6DC067

It seems the last 4 bits are lost, or whole value right shifting a byte...

I tried deleting entire row and run an Insert Query, same things happen!

Can anyone tell me why is this happening & how can I solve it? Thanks!

I have tried several varying length of binary, for maximum 43658 characters (Each represents 4 bits, total around 21 KB), the update query runs normally. 1 more character will make the above "bug" appears...

PS1: For a shorter length varbinary as update value, everything is okay

PS2: I can post whole binary string out if it helps, but it is really long and I am not sure if it's suitable to post here

EDITED: Thanks for any help!

As someone suggested, the value inserted maybe of odd number of 4-bits, so there is a 0 append in front of it. Here is my update information on the value:

The value is of 43677 characters long exluding "0x", which menas Yes, it is odd

It does explain why a '0' is inserted before, but does not explain why the last character disappears...

Then I do an experiment:

I insert a even length value, with me manually add a '0' before the original value,

Now the value to be updated is

0x0FFD8F...6DC0676

which is of 43678 characters long, excluding "0x"

The result is no luck, the updated value is still

0x0FFD8...6DC067

shole
  • 4,046
  • 2
  • 29
  • 69
  • You're right - a big lump of binary data is unlikely to be helpful, but some idea of the queries you're using to do the work might be. – Jon Egerton Apr 28 '15 at 08:11
  • It's the simplest update query as usual: update [table_name] set [column] = 0xFFD8...676 where [id] = xxx – shole Apr 28 '15 at 08:16
  • varbinaries store bytes. If you give it an odd number of hex digits (where each represents four bits) then it needs to add four bits of padding. You're not losing data, you're gaining padding. – Damien_The_Unbeliever Apr 28 '15 at 08:20
  • @Damien_The_Unbeliever the last '6' is lost... also this value comes from another table's varbinary column, which means this is a valid value I think... – shole Apr 28 '15 at 08:24

1 Answers1

2

It seems that the binary constant 0xFFD8F...6DC0676 that you used for update contains odd number of hex digits. And the SqlServer added half-byte at the beginning of the pattern so that it represent whole number of bytes.

You can see the same effect running the following simple query:

select 0x1, 0x104

This will return 0x01 and 0x0104.

The truncation may be due to some limitaions in SSMS, that can be observed in the following experiment:

declare @b varbinary(max)
set @b = 0x123456789ABCDEF0
set @b = convert(varbinary(max), replicate(@b, 65536/datalength(@b)))
select datalength(@b) DataLength, @b Data

The results returned are 65536 and 0x123456789ABCDEF0...EF0123456789ABCD, however if in SSMS I copy Data column I'm getting pattern of 43677 characters length (this is without leading 0x), which is 21838.5 bytes effectively. So it seems you should not (if you do) rely on long binary data values obtained via copy/paste in SSMS.

The reliable alternative can be using intermediate variable:

declare @data varbinary(max)
select @data = DataXXX from Table_XXX where ID = XXX
update Table_YYY set DataYYY = @data where ID = YYY
i-one
  • 5,050
  • 1
  • 28
  • 40
  • Thanks for the answer, see my edited part to elaborate more :) – shole Apr 28 '15 at 08:32
  • Thanks for the answer, just one more question: this "limitation" is irrelevant to the data type range (i.e. varbinary(MAX) = 2^31-1 bytes)? – shole Apr 28 '15 at 09:04
  • 2
    @shole, I'm not sure, I think yes, this is the first time I faced this issue. There is a setting in SSMS: Tools>Options>Query Results>Results to Grid>Max Characters Retrieved>Non XML Data. I tried to increase it, but max of 65535 is allowed. However even with 65536 limit only 40k-something returned. I think that in this case the reliable way is to get data into variable from one table and then use this variable to update data in another table. – i-one Apr 28 '15 at 09:16