SQL Server Insert Varbinary

Question

I need to take a Varbinary(max) from a SQL Server column and insert it into another varbinary column, however my image does not render when displayed via the new field, what am I doing wrong?

I've copied from SQL Server, ie

E7D28201A240202178EF491E413EB4DDBCFB54A15077A989A37A0C5E4E0F143AEE7000CE3F3A70C0E4F342E524C8F4A7243888AA581EC051B030E7A8EF4E233DF.....

and then

update client
set Photo = convert(varbinary(max),'E7D28201A240202178EF491E413EB4DDBCFB54A15077A989A37A0C5E4E0F143AEE7000CE3F3A70C0E4F342E524C8F4A7243888AA581EC051B030E7A8EF4E233DF.....')
where id='40946b09-81be-42b0-ae62-63182acb1e89'

Answer 1

The data is not Base64 encoded as that would have a much larger range of alpha characters, whereas this value is pure hex values (i.e. 0-9, A-F). Also, the example HTML code provided by the O.P. (in a comment on the Question) is:

<img src="data:image;base64,@System.Convert.ToBase64String(Model.PhotoDisplay) 

which shows that the source value of Model.PhotoDisplay is first converted to Base64 so that the data:image;base64 type designation will be correct.

The problem is that SQL Server, when asked to convert the value, does not know that it is already a hex/binary string. You need to tell SQL Server what the format of the source data is. This can be done in two ways:

1. Depending on how you are building this value, you just need to add the 0x prefix and remove the single-quotes:

   UPDATE cl
   SET    cl.Photo = CONVERT(VARBINARY(MAX), 0xE7D28201A24020.....)
   FROM   Client cl
   WHERE  cl.[id] = '40946b09-81be-42b0-ae62-63182acb1e89';
   

2. If you need to keep the value as a string, and are using SQL Server 2008 or newer, then you can use the "style" option of the CONVERT function to tell SQL Server that it is a binary value, and you have the additional option of

   • adding the 0x prefix and using a "style" of 1:

     UPDATE cl
     SET    cl.Photo = CONVERT(VARBINARY(MAX), '0xE7D28201A24020.....', 1)
     FROM   Client cl
     WHERE  cl.[id] = '40946b09-81be-42b0-ae62-63182acb1e89';
     

   • keep the value without the 0x prefix and using a "style" of 2:

     UPDATE cl
     SET    cl.Photo = CONVERT(VARBINARY(MAX), 'E7D28201A24020.....', 2)
     FROM   Client cl
     WHERE  cl.[id] = '40946b09-81be-42b0-ae62-63182acb1e89';
     

But, if this data really is coming from a VARBINARY(MAX) field in another table, then how is it ending up as a string in the first place? The easiest way to move this is to either:

• Transfer the data directly between tables:

  UPDATE cl
  SET    cl.Photo = src.SomeField
  FROM   Client cl
  INNER JOIN SomeTable src
          ON src.JoinField = cl.JoinField
  WHERE something? = somethingElse?;
  

• Use a VARBINARY(MAX) variable:

  DECLARE @Photo VARBINARY(MAX);
  
  SELECT @Photo = src.photo
  FROM   SomeTable src
  WHERE  src.SomeField = ?;
  
  UPDATE cl
  SET    cl.Photo = @Photo
  FROM   Client cl
  WHERE  cl.[id] = '40946b09-81be-42b0-ae62-63182acb1e89';