Updating a varbinary field - extra leading zero added

Question

Updating a varbinary field - extra leading zero added

pavel_hant

SSC Veteran

Points: 219
More actions
February 7, 2018 at 3:39 pm

#327236

Hello,
I would like to be able to update a varbinary field (named Varbinary_Data of varbinary(max) type) with a value copied from another varbinary field. I have to do it with a script, and I cannot use a subquery to get the new data in the update statement.
The data I want to insert in the field looks like 0x504B030400...D04 and it has 43,679 digits (an odd number).
I am using this update statement:
update ImageStore
set Varbinary_Data = CONVERT(varbinary(max), 0x504B030400...D04, 1)
where Id = 1001
The result is that in the updated field the data has an extra leading zero after the leading 0x, like: 0x0504B030400...D04, which makes the image unusable.
Is there a way to do the update avoiding that extra zero to be added? Thanks, Paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

pavel_hant - Wednesday, February 7, 2018 3:39 PM
Hello,
I would like to be able to update a varbinary field (named Varbinary_Data of varbinary(max) type) with a value copied from another varbinary field. I have to do it with a script, and I cannot use a subquery to get the new data in the update statement.
The data I want to insert in the field looks like 0x504B030400...D04 and it has 43,679 digits (an odd number).
I am using this update statement:
update ImageStore
set Varbinary_Data = CONVERT(varbinary(max), 0x504B030400...D04, 1)
where Id = 1001
The result is that in the updated field the data has an extra leading zero after the leading 0x, like: 0x0504B030400...D04, which makes the image unusable.
Is there a way to do the update avoiding that extra zero to be added? Thanks, Paul

Why can't you use a subquery?

pavel_hant SSC Veteran Points: 219 More actions · Answer 2

I cannot use a subquery because I have to send the update script to multiple users to run it against their databases.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

pavel_hant - Wednesday, February 7, 2018 3:39 PM
Hello,
I would like to be able to update a varbinary field (named Varbinary_Data of varbinary(max) type) with a value copied from another varbinary field. I have to do it with a script, and I cannot use a subquery to get the new data in the update statement.
The data I want to insert in the field looks like 0x504B030400...D04 and it has 43,679 digits (an odd number).
I am using this update statement:
update ImageStore
set Varbinary_Data = CONVERT(varbinary(max), 0x504B030400...D04, 1)
where Id = 1001
The result is that in the updated field the data has an extra leading zero after the leading 0x, like: 0x0504B030400...D04, which makes the image unusable.
Is there a way to do the update avoiding that extra zero to be added? Thanks, Paul

You can't save an odd number of digits because VARBINARY only saves whole bytes, which are represented by two nibbles with each nibble being represented by a hex character. There's no way around it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Heh, likely you're getting incorrect data to begin with.

43,679 is the max number of characters you can copy/paste from a string in an SSMS grid, a fun old connect item that MS never got around to fixing.

I highly doubt that very specific number is a coincidence, so probably the hex value is just already wrong.

You can work around this by casting the string as XML and then copying it out.

Cheers!

filipeallins Newbie Points: 1 More actions · Answer 5

Hi!

I know it's been a long time but i had the same problem.

I was able to copy the hole content by using an SELECT statement.

Something like this:

UPDATE
    TABLE1
SET
    IMAGE = (SELECT IMAGE FROM TABLE2 WHERE ID = 9999)
WHERE
    ID = 9999

In my case i was able to make an SELECT from a linked server.