The application language that I use prepends # (0x25) on the front of
image data before storing it in SQL server. It handles this natively,
and can display the images 'directly' from the entity, internally.

However, I'd like to make use of them in Crystal reports.

I have tried casting the image column to varbinary(max) then
varchar(max) then using the substring to drop the first character and
recasting back to image.

select cast(
cast(
substring(stringimage,2,datalength(stringimage)-1)
as varbinary(max))
as image) as select_image
from (select
cast(
cast(foo.image as varbinary(max))
as varchar(max)) as stringimage
from foo) as data

(obviously, I have difficulty posting sample data in a non-binary
newsgroup.... )

This seemed to work O.K. for one test image, but when trying it on
others, the image stops after a while. I think the casting is
stripping 00 (NULL) characters from the image, and that's breaking the
encoding.

Is it possible to select the 2nd byte of an image or varbinary
onwards? (Rather than using string manipulation). Then I can create a
view which I can use in Crystal.

Iain

I don't see the need to convert to varchar here. You might try a CAST to
varbinary(MAX) directly:

SELECT
SUBSTRING(CAST(image AS varbinary(MAX)), 2, DATALENGTH(image)-1)
FROM dbo.foo;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Iain Sharp" <iains (AT) pciltd (DOT) co.uk> wrote

On Tue, 8 Jun 2010 06:48:14 -0500, "Dan Guzman"
<guzmanda (AT) nospam-online (DOT) sbcglobal.net> wrote:

No apparent difference, either I am mistaking the root cause of the
problem, or the substring (and I tried right()) are doing an implicit
conversion to varchar or something...

I am going to try and have the application save the images to file
(which strips the #) and reload as binary data, then see if this data
displays correctly in Crystal. If it does, it's the conversion,
otherwise, it's a problem in Crystal.

Iain

No conversion should occur here. I ran the script below with a 10K hex
value and it ran as expected.

CREATE TABLE dbo.foo([image] image)
INSERT INTO dbo.foo VALUES(0x2500000000000000) --use a longer value to test

SELECT
SUBSTRING(CAST([image] AS varbinary(MAX)), 2, DATALENGTH(image)-1)
FROM dbo.foo;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

On Tue, 08 Jun 2010 13:39:02 +0100, Iain Sharp <iains (AT) pciltd (DOT) co.uk>
wrote:

Okay, dumping the data to a file from the application and reloading it
without the # character allows Crystal to display the image correctly.
So, unless the application is doing something else to the data (and
the manual says,

/image—reads the raw data from FileName, assuming that this
data is an image. An initial hash character (#) is added to
the data before copying the data to Target. (The hash
character is an indicator to show that image data follows.) No
further conversion is performed on the data.
/raw—behaves similarly to the /image switch, except that the
data in FileName is assumed not to be an image; an initial
hash character (#) is not added. No further conversion is
performed on the data.

Data stored /image is what I have, data stored /raw is working
correctly. So I presume it's the substring() or right() that is
corrupting it....

Regards,

Iain

Iain Sharp (iains (AT) pciltd (DOT) co.uk) writes:
I would suggest that you look at the dump from the application in a binary
editor and compare with what you have in the database, not the least
after the stripping. One possility is that this small-endian thing causes
a byte swap.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 08 Jun 2010 22:32:43 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Found it.

I'd forgotten that the app maintains database independance by chopping
blobs up into segments and storing segments 2+ in a different database
file.

I will now have to step up my plans to change the data types in the
application code to prevent it doing this...

So it's not a SQL problem at all.

Iain