How to convert to regular text, data stored in Image data type field ????

[SD]

Hi,

This is driving me nuts, I have a table that stores notes regarding an
operation in an IMAGE data type field in MS SQL Server 2000.

I can read and write no problem using Access using the StrConv function and
I can Update the field correctly in T-SQL using:

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(BITS_data)
FROM mytable_BINARY WHERE ID = 'RB215'

WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'

However, I just can not seem to be able to convert back to text the
information once it is stored using T-SQL.

My selects keep returning bin data.

How to do this! Thanks for your help.

SD

[Erland Sommarskog]

SD ([e-mail]) writes:

This is driving me nuts, I have a table that stores notes regarding an
operation in an IMAGE data type field in MS SQL Server 2000.

I can read and write no problem using Access using the StrConv function
and I can Update the field correctly in T-SQL using:

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(BITS_data)
FROM mytable_BINARY WHERE ID = 'RB215'

WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'

However, I just can not seem to be able to convert back to text the
information once it is stored using T-SQL.

My selects keep returning bin data.

You can do:

create table img(a image)
go
insert img(a) values (0x41434549)
go
select convert(varchar(8000), convert(binary(8000), a)) from img

But obviously you would not have chosen image, if your data is
less than 8000 bytes, so can only get piece by piece this way.

It may be better to do it client-side, for instance that StrConv
function in Access.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp