Prev: Grouping stored procedures
Next: image field where clause
From: JeffP- on 10 Jun 2010 12:35
It was not my choice to use an image field to store text in software released
in 2009, but here it is...

I'm trying to debug a script that is writing updates from another database
to the history log that uses an image field to store the text data. All is
well using either writeText or updateText w/out errors.

My challenge is to easily view the results of the image field in my query
for debugging.

Image data field contains 'Hello World' in the notes field
the value appears as: 0x48656C6C6F20576F726C64

1. Is there a way in an inline query to view as 'Hello World' ?

2. Is there any other way to view as 'Hello World'

TIA
From: John Bell on 10 Jun 2010 14:26
On Thu, 10 Jun 2010 09:35:25 -0700, JeffP->
<JeffP(a)discussions.microsoft.com> wrote:

>It was not my choice to use an image field to store text in software released
>in 2009, but here it is...
>
>I'm trying to debug a script that is writing updates from another database
>to the history log that uses an image field to store the text data. All is
>well using either writeText or updateText w/out errors.
>
>My challenge is to easily view the results of the image field in my query
>for debugging.
>
>Image data field contains 'Hello World' in the notes field
>the value appears as: 0x48656C6C6F20576F726C64
>
>1. Is there a way in an inline query to view as 'Hello World' ?
>
>2. Is there any other way to view as 'Hello World'
>
>TIA

As in

CREATE TABLE #tmpimage ( imagetext image );

INSERT INTO #tmpimage ( imagetext ) VALUES ( 'Hello World' );

SELECT imagetext, CAST(CAST(imagetext as varbinary(max)) as
varchar(max)) FROM #tmpimage

DROP TABLE #tmpimage

John
From: JeffP- on 10 Jun 2010 17:40
Thank you John,

i tried flavors of those casts but not together, I'm sure there's a good
reason but I'd expected something on MSDN or similar to have popped up on one
of my many prior web searches...

....maybe a udf - fn_showTxt(field,startbit,stopbit)

"John Bell" wrote:

> On Thu, 10 Jun 2010 09:35:25 -0700, JeffP->
> <JeffP(a)discussions.microsoft.com> wrote:
>
> >It was not my choice to use an image field to store text in software released
> >in 2009, but here it is...
> >
> >I'm trying to debug a script that is writing updates from another database
> >to the history log that uses an image field to store the text data. All is
> >well using either writeText or updateText w/out errors.
> >
> >My challenge is to easily view the results of the image field in my query
> >for debugging.
> >
> >Image data field contains 'Hello World' in the notes field
> >the value appears as: 0x48656C6C6F20576F726C64
> >
> >1. Is there a way in an inline query to view as 'Hello World' ?
> >
> >2. Is there any other way to view as 'Hello World'
> >
> >TIA
>
> As in
>
> CREATE TABLE #tmpimage ( imagetext image );
>
> INSERT INTO #tmpimage ( imagetext ) VALUES ( 'Hello World' );
>
> SELECT imagetext, CAST(CAST(imagetext as varbinary(max)) as
> varchar(max)) FROM #tmpimage
>
> DROP TABLE #tmpimage
>
> John
> .
>
From: John Bell on 10 Jun 2010 18:13
On Thu, 10 Jun 2010 14:40:19 -0700, JeffP->
<JeffP(a)discussions.microsoft.com> wrote:

>Thank you John,
>
>i tried flavors of those casts but not together, I'm sure there's a good
>reason but I'd expected something on MSDN or similar to have popped up on one
>of my many prior web searches...
>
>...maybe a udf - fn_showTxt(field,startbit,stopbit)
>
>"John Bell" wrote:
>
>> On Thu, 10 Jun 2010 09:35:25 -0700, JeffP->
>> <JeffP(a)discussions.microsoft.com> wrote:
>>
>> >It was not my choice to use an image field to store text in software released
>> >in 2009, but here it is...
>> >
>> >I'm trying to debug a script that is writing updates from another database
>> >to the history log that uses an image field to store the text data. All is
>> >well using either writeText or updateText w/out errors.
>> >
>> >My challenge is to easily view the results of the image field in my query
>> >for debugging.
>> >
>> >Image data field contains 'Hello World' in the notes field
>> >the value appears as: 0x48656C6C6F20576F726C64
>> >
>> >1. Is there a way in an inline query to view as 'Hello World' ?
>> >
>> >2. Is there any other way to view as 'Hello World'
>> >
>> >TIA
>>
>> As in
>>
>> CREATE TABLE #tmpimage ( imagetext image );
>>
>> INSERT INTO #tmpimage ( imagetext ) VALUES ( 'Hello World' );
>>
>> SELECT imagetext, CAST(CAST(imagetext as varbinary(max)) as
>> varchar(max)) FROM #tmpimage
>>
>> DROP TABLE #tmpimage
>>
>> John
>> .
>>

Under CAST and CONVERT in BOL you can see that is no direct route and
image can only be CAST to binary/varbinary or timestamp. Which you are
allowed to cast to varchar.

John
 | 
Pages: 1
Prev: Grouping stored procedures
Next: image field where clause