We help IT Professionals succeed at work.

Insert HEX value into SQL Server Image field.

parkstech
parkstech asked
on
I have tried converting and casting in SQL.

I have a hexidecimel string which I need to insert into an image field of a SQL Server 2005 table.
When I do this and run a select on the table the value is not what I inserted.

Help please.

Value = "3c444154413e3c5343484544554c45205343484544554c4549443d225363686564756c6530353339443545332d313531352d373041312d374539392d33353644393331453631393922204e414d453d22445f47544d535052445f44424122204652455155454e4359545950453d224441494c5922205354415254444154453d2231312f30322f323030392220454e44444154453d22222049535055424c49433d2274727565222049535348415245443d227472756522205553455249443d22726b727573736522204f524749443d226c756d6967656e74223e3c4441494c59204146544552444159533d2231222f3e3c54494d4520545950453d224f4e4345223e3c4f4e4345204f4e434554494d453d2232323a33353a3030222f3e3c52455045415420494e54455256414c3d22312220535441525454494d453d222220454e4454494d453d22222f3e3c2f54494d453e3c2f5343484544554c453e3c2f444154413e"

Thank you
Comment
Watch Question

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
An example

create table hexx1 (i image)
insert hexx1
select '0x3c444154413e3c5343484544554c45205343484544554c4549443d225363686564756c6530353339443545332d313531352d373041312d374539392d33353644393331453631393922204e414d453d22445f47544d535052445f44424122204652455155454e4359545950453d224441494c5922205354415254444154453d2231312f30322f323030392220454e44444154453d22222049535055424c49433d2274727565222049535348415245443d227472756522205553455249443d22726b727573736522204f524749443d226c756d6967656e74223e3c4441494c59204146544552444159533d2231222f3e3c54494d4520545950453d224f4e4345223e3c4f4e4345204f4e434554494d453d2232323a33353a3030222f3e3c52455045415420494e54455256414c3d22312220535441525454494d453d222220454e4454494d453d22222f3e3c2f54494d453e3c2f5343484544554c453e3c2f444154413e'

i.e. prefix with "0x"

Author

Commented:
This does not work, 3 of tried it.

We inserted this value:

0x3c444154413e3c5343484544554c45205343484544554c4549443d225363686564756c6530353339443545332d313531352d373041312d374539392d33353644393331453631393922204e414d453d22445f47544d535052445f44424122204652455155454e4359545950453d224441494c5922205354415254444154453d2231312f30322f323030392220454e44444154453d22222049535055424c49433d2274727565222049535348415245443d227472756522205553455249443d22726b727573736522204f524749443d226c756d6967656e74223e3c4441494c59204146544552444159533d2231222f3e3c54494d4520545950453d224f4e4345223e3c4f4e4345204f4e434554494d453d2232323a33353a3030222f3e3c52455045415420494e54455256414c3d22312220535441525454494d453d222220454e4454494d453d22222f3e3c2f54494d453e3c2f5343484544554c453e3c2f444154413e

The table contained this value:

0x30783363343434313534343133653061303930393363353334333438343534343535346334353230353334333438343534343535346334353439343433643232353336333638363536343735366336353330333533333339343433353435333332643331333533313335326433373330343133313264333734353339333932643333333533363434333933333331343533363331333933393232323034653431346434353364323234343566343735343464353335303532343435663434343234313232323034363532343535313535343534653433353935343539353034353364323234343431343934633539323232303533353434313532353434343431353434353364323233313331326633303332326633323330333033393232323034353465343434343431353434353364323232323230343935333530353534323463343934333364323237343732373536353232323034393533353334383431353234353434336432323734373237353635323232303535353334353532343934343364323236313737373433323338333733313232323034663532343734393434336432323663373536643639363736353665373432323365306130393039336334343431343934633539323034313436353434353532343434313539353333643232333132323266336530613039303933633534343934643435323035343539353034353364323234663465343334353232336530613039303930393363346634653433343532303466346534333435353434393464343533643232333233323361333333353361333033303232326633653061303930393039336335323435353034353431353432303439346535343435353235363431346333643232333132323230353335343431353235343534343934643435336432323232323034353465343435343439346434353364323232323266336530613039303933633266353434393464343533653061303930393363326635333433343834353434353534633435336530613061303933633266343434313534343133653061

This is not the value that should be in that field.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Okay, my mistake.

Do not quote the string.

insert hexx1
select 0x3c444154413e3c5343484544554c45205343484544554c4549443d225363686564756c6530353339443545332d313531352d373041312d374539392d33353644393331453631393922204e414d453d22445f47544d535052445f44424122204652455155454e4359545950453d224441494c5922205354415254444154453d2231312f30322f323030392220454e44444154453d22222049535055424c49433d2274727565222049535348415245443d227472756522205553455249443d22726b727573736522204f524749443d226c756d6967656e74223e3c4441494c59204146544552444159533d2231222f3e3c54494d4520545950453d224f4e4345223e3c4f4e4345204f4e434554494d453d2232323a33353a3030222f3e3c52455045415420494e54455256414c3d22312220535441525454494d453d222220454e4454494d453d22222f3e3c2f54494d453e3c2f5343484544554c453e3c2f444154413e

Just prefix the whole thing with 0x
This was an honest over sight on everyone's part.  Remove the single quotes from the HEX string before inserting the string into the image filed.