Insert varbinary(max): data with a zero

Question

Insert varbinary(max): data with a zero

JoseACJr

SSC Eights!

Points: 934
More actions
November 30, 2012 at 11:38 am

#285425

Hi,
I have one table:
create table Files(
Id int identity not null,
name varchar(20),
document varbinary(max)
)
go
--
insert into Files (name, document)
values ('file 1', (select * from OPENROWSET(BULK '\\maq05\publico\doc1.doc', SINGLE_BLOB) AS myDoc) )
go
select * from Files
go
Result:
Id name document
1 file 1 0xD0CF11E0A1B11AE10...
I 'm going after insert using a variable containing the same value, see:
declare @VarVb varbinary(max)
set @VarVb = 0xD0CF11E0A1B11AE10... ; -- same varbinary
insert into Files (name, document)
values ('file 2', @varVb )
go
Result:
Id name document
1 file 1 0xD0CF11E0A1B11AE10...
2 file 2 0x0D0CF11E0A1B11AE10... -- with a zero in the third position 🙁
I use convert, cast but a zero continues...
Can you help me?
Thanks !!
Jose Anchieta C. Jr

Viewing 3 posts - 1 through 2 (of 2 total)

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

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 1

Are you sure both varbinary values are the same? SQL Server will always insert a leading zero if there are an odd number of hex digits after the initial 0x.

So

declare @VarVb varbinary(max)

set @VarVb = 0xD0CF01

select @VarVb

set @VarVb = 0xD0CF0

select @VarVb

Returns

--------

0xD0CF01

--------

0x0D0CF0

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

JoseACJr SSC Eights! Points: 934 More actions · Answer 2

Hi Richard,

Varbinary values are the same..

In your example I noticed that was also inserted a zero because it is a odd numbers of digits after 0x

Is there any way to insert the same values ??with Varbinary ?

Thanks,

Jose Anchieta C. Jr