Insert hex string value to sql server image field is appending extra 0

Question

Have an image field and want to insert into this from a hex string:

insert into imageTable(imageField) 
values(convert(image, 0x3C3F78...))

however when I run select the value is return with an extra 0 as 0x03C3F78...

This extra 0 is causing a problem in another application, I dont want it.

How to stop the extra 0 being added?

The schema is:

CREATE TABLE [dbo].[templates](
    [templateId] [int] IDENTITY(1,1) NOT NULL,
    [templateName] [nvarchar](50) NOT NULL,
    [templateBody] [image] NOT NULL,
    [templateType] [int] NULL)

and the query is:

insert into templates(templateName, templateBody, templateType) 
values('I love stackoverflow', convert(image, 0x3C3F786D6C2076657273696F6E3D.......), 2)

the actual hex string is quite large to post here.

Answer 1

I have just had similar problem and I blame myself.

It is possible, that you copy just part of data you need. In my case, I added '0' to the end of the blob.

The cause of this could be copying the value from SQL Management Studio to clipboard.

---

insert into imageTable(imageField) values(0x3C3F78...A)

Select returned: 0x03C3F78...

---

insert into imageTable(imageField) values(0x3C3F78...A 0 )

Select returned: 0x3C3F78...

---

I hope this will help.

Best wishes.

Answer 2

This is correct for 0x0: each pair of digits makes one byte so 0x00 has the value stored

When I run SELECT convert(varbinary(max), 0x55) I get 0x55 out on SQL Server 2008. SELECT convert(varbinary(max), 85) gives me 0x00000055 which is correct is 85 is a 32 bit integer

What datatype are you casting to varbinary?

Edit: I still can't reproduce using image not varbinary

Some questions though:

• is this an upgraded database? What is the compatibility level?
• why use image: use varbinary(max) instead
• what happens when you change everything to varbinary?