UPDATE datatype image in SQL-Table

Question

I've a table cSc_Role with a column RoleSettings. RoleSettings is datatype image. The Content is something like this: 0x504B030414000000080000000000E637CA2A4

Now I need to update this column for one row. Like this:

UPDATE cSc_Role
SET RoleSettings = '0x343240000000000E637CA2A430500'
WHERE Naming = 'User X'

But with binary data it seems like this is not possible to do it with a string. The other option is, I can provide the image in a temporary .bak file. And then do an INSERT INTO. But with this solution I've read it is only possible to insert a complete row and not only a column. Or can I insert only a column with insert?

How can I update or insert one image-column in a table? Thanks in advance.

Answer 1

If above all solution did not work then try to update like below by removing '' in following ,

UPDATE cSc_Role
SET RoleSettings = 0x343240000000000E637CA2A430500
WHERE Naming = 'User X'

Also, avoid using these data types (ntext, text, and image) in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Answer 2

I had a same problem on project, you need to provide binary data as hex value, not string. This tool can make you UPDATE or INSERT that you need

Binary-file-to-sql-hexstring

It has saved me a lot of man hours.

Answer 3

Use a hex-literal, x'34....'

UPDATE cSc_Role
SET RoleSettings = x'343240000000000E637CA2A430500'
WHERE Naming = 'User X'

Answer 4

Try to use convert to varbinary:

UPDATE cSc_Role
SET RoleSettings = convert(VARBINARY(MAX),'0x343240000000000E637CA2A430500')
WHERE Naming = 'User X'