 |
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

May 17th, 2004, 05:34 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Convert from Image to Text
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
|

May 17th, 2004, 05:42 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 17th, 2004, 08:21 AM
|
Registered User
|
|
Join Date: May 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 17th, 2004, 09:32 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 18th, 2004, 08:10 AM
|
Registered User
|
|
Join Date: May 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

May 18th, 2004, 08:59 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 18th, 2004, 10:51 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

October 22nd, 2004, 12:48 PM
|
Registered User
|
|
Join Date: Oct 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

February 24th, 2007, 01:31 PM
|
Registered User
|
|
Join Date: Feb 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

February 24th, 2007, 02:17 PM
|
Registered User
|
|
Join Date: Feb 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|
|
 |