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.

Robert · Accepted Answer · 2015-04-23 08:58:45Z

2

Try to use convert to varbinary:

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

edited Apr 23, 2015 at 8:58

answered Apr 23, 2015 at 8:30

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1673665 Over a year ago

this works (u just missed an e in convert). And it converts the given string into hex. So, what I want to copy into RoleSettings is the content from another database, where I copied the content into a textfile. So, I think first I need to convert this content into decimal, and then convert it back with update into hex?

Robert Over a year ago

@user1673665 I'm not sure but maybe you're right. Try it then you will know :)

Pedram · Accepted Answer · 2015-12-30 08:18:27Z

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.

answered Dec 30, 2015 at 8:18

Pedram

6,55811 gold badges69 silver badges93 bronze badges

Comments

jarlh · Accepted Answer · 2015-04-23 07:51:28Z

0

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

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

answered Apr 23, 2015 at 7:51

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

3 Comments

user1673665 Over a year ago

Thank you for your answer. But when I try this I get error wrong syntax. I put the letter x before the ' and removed the 0x at the beginning.

jarlh Over a year ago

Which dbms are you using? (My answer is ANSI SQL compliant...)

user1673665 Over a year ago

MS SQL Server Management Studio 2008 R2

Ivan Marjanovic · Accepted Answer · 2018-01-05 14:04:10Z

0

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.

answered Jan 5, 2018 at 14:04

Ivan Marjanovic

1,0341 gold badge10 silver badges16 bronze badges

Collectives™ on Stack Overflow

UPDATE datatype image in SQL-Table

4 Answers 4

2 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related