Hi,

I have a column defined as an Image data type. This field is used to store large amounts of text. Now why this field wasnt set as a text type I dont know, the database was designed by someone else.

I basically want this Image field to be converted to text so that I can more easily use it in reports. Any ideas on how to do this? SQL Server doesnt allow this conversion.

Thanks
Niall

Just an idea here, but how about pulling the image into an ADO object (VB or C or something), then writing it back into the text field? you'd probably have to 'manage' the conversion yourself - pass the output into variant or object variable before trying to write back to the db.
It's a million-to-one shot, but it just might work

Chris


There are two secrets to success in this world:
1. Never tell everything you know

I tried this (although without data in the fields) and didn't get any complaints:

1. right click on your db and choose export data.

2. export the old tabe to the new one: you'll get to indicate source and destination db, choose accordingly. The you'll get to a list of tables to export: choose your old table. In the same interface you''l be able to indicate the destination table, give a new name there. Also there is a button 'transformations'. There's the trick: click it and change the target fieldtype (source is fixed of course) to text.

Let me know if it works with data inside

I recon that perhaps the data will be mangled because image and text are stored differently, but you'll have to try to know....


cheers

Hi,

Thanks, that was a good try but it didnt work. It just left the new text field as NULL, it didnt transfer anything from the image field. It didnt even throw an errors which maybe you'd expect. I was expecting an error as this conversion is not allowed by sql.

Thanks anyways
Niall

Look in your msdn help for

TEXTPTR

you can declare a textptr to your image field, READTEXT it's context and WRITETEXT it back to a text field.

Since the data has been/is being put in an image field anyhow, it should work that way. I guess that SQL doesn't give errormessages in what you've tried or in my earlier suggestion because the actual data are one pointerhop further away and that the used methods ignore that hop.

Thanks for the reply.

I had initially tried this also with the following sql..

DECLARE @val varbinary(16)
SELECT @val = TEXTPTR(problemcomment)
FROM c_ticketactivity_ext
WHERE ticketactivityid = 'Q6UJ9A09W01D'
READTEXT c_ticketactivity_ext.problemcomment @val 0 0

writetext c_ticketactivity_ext.problemcomment2 @val

The problem is what to add to the end of the writetext statement? It needs to be the readtext value from the image field but the ReadText only returns a pointer, not the actually text the user typed into field. How can you capture this and pass it to the writetext command??

Thanks
Niall

I almost have it.....


DECLARE @val varbinary(16),@val2 varbinary(16)
SELECT @val = TEXTPTR(problemcomment)
FROM c_ticketactivity_ext
WHERE ticketactivityid = 'Q6UJ9A02C6RC'
READTEXT c_ticketactivity_ext.problemcomment @val 0 0

SELECT @val2 = TEXTPTR(problemcomment2)
FROM c_ticketactivity_ext
WHERE ticketactivityid = 'Q6UJ9A02C6RC'
READTEXT c_ticketactivity_ext.problemcomment2 @val2 0 0

writetext c_ticketactivity_ext.problemcomment2 @val2 @val

the problem now is that what is written to the text field (problemcomment2) is just a meaningless string...e.g. þÿ¯¬

any ideas?

In case you haven't figured it out yet....

  -- insert as space as a placeholder
  -- into the new text col to get valid texptrs later
  update the_table
    set new_text_col = ' '

  -- get text ptrs for the row you'd like to update
  select
    @old_img_ptr = TEXTPTR(old_img_col),
    @new_text_ptr = TEXTPTR(new_text_col)
  from the_table
  where the_identity_col= @somevalue

  -- if we have a good textptr from the old img col
  -- update the new text col with the data,
  -- starting at offset 0, deleting our placeholder char
  if ( TEXTVALID('the_table.old_img_col', @old_img_ptr) = 1 )
    updatetext the_table.new_text_col @new_text_ptr 0 1 the_table.old_img_col @old_img_ptr
  -- if there was no data in the source text col, null out the img col
  else
    writetext the_table.new_text_col @new_text_ptr NULL

  -- show the resulting data lengths (for debug purposes only)
  select
    datalength(old_img_col) old_img_len,
    datalength(new_text_col) new_text_len
  from the_table
  where the_identity_col= @somevalue

You can put this in a while loop to process all rows in the table.

I still get symbols when runing this code.

update paymentdetails2 set image_as_text = ' '
declare @old_img_ptr varbinary(16)
declare @new_text_ptr varbinary(16)
  -- get text ptrs for the row you'd like to update
  select
    @old_img_ptr = TEXTPTR(pay_slip_Image),
    @new_text_ptr = TEXTPTR(image_as_text)
  from paymentdetails2
  where id= '61757685-0A5B-4388-A415-0291DDDE039A'

  -- if we have a good textptr from the old img col
  -- update the new text col with the data,
  -- starting at offset 0, deleting our placeholder char
  if ( TEXTVALID('paymentdetails2.pay_slip_Image', @old_img_ptr) = 1 )
    updatetext paymentdetails2.image_as_text @new_text_ptr 0 1 paymentdetails2.pay_slip_Image @old_img_ptr
  -- if there was no data in the source text col, null out the img col
  else
    writetext paymentdetails2.image_as_text @new_text_ptr NULL

although the size of both match after this query
  select
    datalength(pay_slip_Image) old_img_len,
    datalength(image_as_text) new_text_len
  from paymentdetails2
  where id= '61757685-0A5B-4388-A415-0291DDDE039A'

The content of the text column is this = ÿØÿà

Any idea?

Thank you

And sorry BTW;

That code has the same results that this code

update paymentdetails2
set image_as_text = convert(varchar(max), convert(varbinary(16), pay_slip_image))
where id= '61757685-0A5B-4388-A415-0291DDDE039A'

Any idea?