We have a database hosted on our SQL Server 2016 server that has email content stored in an IMAGE type column. I am trying to convert the data to its string representation. Everything I've read says that I should be able to just use CONVERT(VARCHAR(MAX), Field, 1) but that's not working. I've tried the following as a test:
declare @body varbinary(max);
select @body = substring(cast(body as varbinary(max)),1,13) from dbo.arc_bodies where id = -2147467502
select @body, convert(varchar(1000), @body, 1), convert(varchar(1000), 0x3C68746D6C3E)
So I take the first 13 bytes (just for testing I kept the data short instead of grabbing the whole column) from the "body" IMAGE field and convert it to VARBINARY. In the last statement, I show the contents of @body, try the convert, and try the convert by just copying the raw data.
This is what I end up with:
(No column name) (No column name) (No column name)
0x3C68746D6C3E0D0A3C68656164 0x3C68746D6C3E0D0A3C68656164 <html>
The convert of the @body returns the exact same data as the raw binary but the convert of the raw data works. What am I doing wrong?
In 3rd column in the last query you don't use any style. Just do the same for the 2nd one:
select @body, convert(varchar(1000), @body), convert(varchar(1000), 0x3C68746D6C3E)