Image Data Type

How do I extract the text or readable data from a column
that is defined as an image data type?Is the READTEXT supposed to return Hex values?
select ImageData from afw_image where imageid = '1998-02-
11 12:49:30.283'
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(ImageData)
FROM afw_image
WHERE imageid = '1998-02-11 12:49:30.283'
READTEXT afw_image.ImageData @.ptrval 1 25
GO
ImageData
---
---
---
---
--
0x204D41494C494E472041444452455353204348414E474520393630343
232204546464543544956452030342F31352F393620504C454153452020
20200D0A495353554520414E20454E444F5253454D454E5420574954482
05448452020464F4C4C4F57494E47204348414E4745533A202020504C45
41534520200D0A414D45
(1 row(s) affected)
ImageData
---
---
---
---
--
0x4D41494C494E472041444452455353204348414E4745203936
(1 row(s) affected)
>--Original Message--
>Dave,
>Refer READTEXT in BooksOnLine.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Dave M" <dmunsterman@.appliedsystems.com> wrote in message
>news:8f5a01c35b6f$64f3e9b0$a001280a@.phx.gbl...
>> How do I extract the text or readable data from a column
>> that is defined as an image data type?
>
>.
>

I'm not sure why this one fails,

I used the following SQL to extract data from our Firms emplyee database and
creates a text file that gets imported into our copy machine controllers.
Output lines look like this:
A|U|4111|MPLS, PBMS|01|0||
A|U|4222|RIC, PBMS|06|0||
A|U|2036|user, name|01|0|N|1234562|
The last number is the last 7 of the persons corporate VISA card. On SQL
2000 this SQL worked:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' , _visano , '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
On SQL 2005 it doesn't: The sql runs but only delivers those with that
VISA Numeber, where it use to give you both those with and without cards.
Any Ideas?When you don't have a credit card number, are you storing a NULL instead?
Are you getting any errors? How are you creating the file?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jay Bukstein" <JayBukstein@.discussions.microsoft.com> wrote in message
news:697B4582-6D4F-47BD-B5DF-F1AD95422D0C@.microsoft.com...
I used the following SQL to extract data from our Firms emplyee database and
creates a text file that gets imported into our copy machine controllers.
Output lines look like this:
A|U|4111|MPLS, PBMS|01|0||
A|U|4222|RIC, PBMS|06|0||
A|U|2036|user, name|01|0|N|1234562|
The last number is the last 7 of the persons corporate VISA card. On SQL
2000 this SQL worked:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' , _visano , '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
On SQL 2005 it doesn't: The sql runs but only delivers those with that
VISA Numeber, where it use to give you both those with and without cards.
Any Ideas?|||Hi Jay
a quick question, is _visano a column in the HBM_PERSNL table?
I noticed that at the end of the query you are using a comma instead of + to
concat the string, should the query be:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' + _visano + '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
Lucas
"Jay Bukstein" wrote:

> I used the following SQL to extract data from our Firms emplyee database a
nd
> creates a text file that gets imported into our copy machine controllers.
> Output lines look like this:
> A|U|4111|MPLS, PBMS|01|0||
> A|U|4222|RIC, PBMS|06|0||
> A|U|2036|user, name|01|0|N|1234562|
> The last number is the last 7 of the persons corporate VISA card. On SQL
> 2000 this SQL worked:
> select 'A|U|' +
> RTRIM(EMPLOYEE_CODE) + '|'
> + RTRIM(EMPLOYEE_NAME) + '|'
> + rtrim(offc) + '|0|' +
> case position
> when 'partner' then 'P'
> else 'N'
> end
> + '|' , _visano , '|'
> from HBM_PERSNL
> where INACTIVE = 'N'
> and EMPLOYEE_NAME NOT like '%CMS%'
> and EMPLOYEE_NAME NOT Like '%billing%'
> and EMPLOYEE_NAME NOT Like '%temp%'
> On SQL 2005 it doesn't: The sql runs but only delivers those with that
> VISA Numeber, where it use to give you both those with and without cards.
> Any Ideas?
>|||Sounds like your server or session defaults on '05 yield null on
concatenation; i.e., select 'a' + NULL yields NULL whereas on 2000 select
'a' + NULL yields 'a'.
Suggest to modify the query to
....
'|' + ISNULL(_visano, '') + '|'
...
In fact, this is a good practice for any columns that allow null. That way
changes to server or session settings always yield the same results.
"Jay Bukstein" <JayBukstein@.discussions.microsoft.com> wrote in message
news:697B4582-6D4F-47BD-B5DF-F1AD95422D0C@.microsoft.com...
>I used the following SQL to extract data from our Firms emplyee database
>and
> creates a text file that gets imported into our copy machine controllers.
> Output lines look like this:
> A|U|4111|MPLS, PBMS|01|0||
> A|U|4222|RIC, PBMS|06|0||
> A|U|2036|user, name|01|0|N|1234562|
> The last number is the last 7 of the persons corporate VISA card. On SQL
> 2000 this SQL worked:
> select 'A|U|' +
> RTRIM(EMPLOYEE_CODE) + '|'
> + RTRIM(EMPLOYEE_NAME) + '|'
> + rtrim(offc) + '|0|' +
> case position
> when 'partner' then 'P'
> else 'N'
> end
> + '|' , _visano , '|'
> from HBM_PERSNL
> where INACTIVE = 'N'
> and EMPLOYEE_NAME NOT like '%CMS%'
> and EMPLOYEE_NAME NOT Like '%billing%'
> and EMPLOYEE_NAME NOT Like '%temp%'
> On SQL 2005 it doesn't: The sql runs but only delivers those with that
> VISA Numeber, where it use to give you both those with and without cards.
> Any Ideas?
>