Additional 0 in varbinary insert

Question

I have a problem when I am trying to move a varbinary(max) field from one DB to another. If I insert like this: 0xD0CF11E0A1B11AE10000000 It results the beginning with an additional '0': 0x0D0CF11E0A1B11AE10000000

And I cannot get rid of this. I've tried many tools, like SSMS export tool or BCP, but without any success. And it would be better fro me to solve it in a script anyway.

And don't have much kowledge about varbinary (a program generates it), my only goal is to copy it:)

Answer 1

0xD0CF11E0A1B11AE10000000

This value contains an odd number of characters. Varbinary stores bytes. Each byte is represented by exactly two hexadecimal characters. You're either missing a character, or your not storing bytes.

Here, SQL Server is guessing that the most significant digit is a zero, which would not change the numeric value of the string. For example:

select 0xD0C "value"
    ,cast(0xD0C as int) "as_integer"
    ,cast(0x0D0C as int) "leading_zero"
    ,cast(0xD0C0 as int) "trailing_zero"


 value       3_char     leading_zero     trailing_zero    
 ----------  ---------  ---------------  ---------------- 
 0d0c        3340       3340             53440            

Or:

select 1 "test"
where 0xD0C = 0x0D0C

 test    
 ------- 
 1       

It's just a difference of SQL Server assuming that varbinary always represents bytes.