I have a problem displaying a BLOB from a view in MSSQL 2005. Running the same query against one of the tables that make up the view, it works fine.

Master for table:
FIELDNAME=GUID, ALIAS=guid, USAGE=A38, ACTUAL=A38, FIELDTYPE=R, $
FIELDNAME=BUS_TRACKING_EVENT_GUID, ALIAS=bus_tracking_event_guid, USAGE=A38, ACTUAL=A38, FIELDTYPE=R, $
FIELDNAME=IMAGE_DATA, ALIAS=image_data, USAGE=BLOB, ACTUAL=BLOB, FIELDTYPE=R, $

Master for view:
FIELDNAME=DATE_TIME_STAMP, ALIAS=date_time_stamp, USAGE=YYMD, ACTUAL=DATE, FIELDTYPE=R, $
FIELDNAME=EVENT_TYPE_NAME, ALIAS=event_type_name, USAGE=A128, ACTUAL=A128V, FIELDTYPE=R, $
FIELDNAME=EVENT_GROUP_NAME, ALIAS=event_group_name, USAGE=A128, ACTUAL=A128V, FIELDTYPE=R, $
FIELDNAME=EVENT_CLASS_NAME, ALIAS=event_class_name, USAGE=A128, ACTUAL=A128V, FIELDTYPE=R, $
FIELDNAME=STOCK_ID, ALIAS=stock_id, USAGE=A13, ACTUAL=A13V, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=DESIGN_DESCRIPTION, ALIAS=design_description, USAGE=A90, ACTUAL=A90V, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=USER_NAME, ALIAS=user_name, USAGE=A60, ACTUAL=A60V, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=OPS_MANAGER_NAME, ALIAS=ops_manager_name, USAGE=A30, ACTUAL=A30, FIELDTYPE=R, $
FIELDNAME=CONTRACTOR_NAME, ALIAS=contractor_name, USAGE=A60, ACTUAL=A60V, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=SITE_CODE, ALIAS=site_code, USAGE=A24, ACTUAL=A24V, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=STATION_NAME, ALIAS=station_name, USAGE=A30, ACTUAL=A30, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=SITE_TYPE, ALIAS=site_type, USAGE=A30, ACTUAL=A30, FIELDTYPE=R, $
FIELDNAME=SUB_SITE_TYPE, ALIAS=sub_site_type, USAGE=A30, ACTUAL=A30, FIELDTYPE=R, $
FIELDNAME=NOTES, ALIAS=notes, USAGE=A200, ACTUAL=A200V, FIELDTYPE=R,
MISSING=ON, $
FIELDNAME=IMAGE_DATA, ALIAS=image_data, USAGE=BLOB, ACTUAL=BLOB, FIELDTYPE=R, $

This is the trace:

ENGINE SQLMSS SET SERVER VOLIVETDI
SET SQLENGINE = SQLMSS
SQL SQLMSS SET VARCHAR OFF
SET XRETRIEVAL=OFF

-* table
TABLE FILE tablename
PRINT BUS_TRACKING_EVENT_GUID IMAGE_DATA
BY BUS_TRACKING_EVENT_GUID NOPRINT
ON TABLE SET GRID OFF
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=IMAGE_DATA, IMAGE=('IMAGE_DATA'), SIZE=(0.5 0.5), PRESERVERATIO=ON, $
ENDSTYLE
END

-* view
TABLE FILE view
PRINT DATE_TIME_STAMP IMAGE_DATA
BY DATE_TIME_STAMP NOPRINT
ON TABLE SET GRID OFF
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=IMAGE_DATA, IMAGE=('IMAGE_DATA'), SIZE=(0.5 0.5), PRESERVERATIO=ON, $
ENDSTYLE
END

-* table
14.23.45 BS (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
14.23.45 BS (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
14.23.45 AE SELECT T1."bus_tracking_event_guid",T1."image_data" FROM
14.23.45 AE TDI.dbo.table T1 ORDER BY
14.23.45 AE T1."bus_tracking_event_guid";
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
14.23.45 BS (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
14.23.45 BS (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED

-* View
14.23.45 AE SELECT T1."date_time_stamp",T1."image_data" FROM
14.23.45 AE TDI.view T1 ORDER BY
14.23.45 AE T1."date_time_stamp";
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

It appears that the first row that have a null value in the image column disables the display of the image for the rest of the report. We've made an addition to the view, adding is_image_present(1 or 0). I was trying using that to populate a DEFINEd field with a default picture if there were no image, but no luck. If I sort the report by highest is_image_present all images appear.

Can anybody give me any idea why this happens?

Tia
Håkan

This message has been edited. Last edited by: Kerry,

Hi Hakan,

I consulted with our technicals and was suggested that, if this is not resolved, please open a case with Customer Support Services so that we can take a detailed look at the issue for further research. You may either call at 1-800-736-6130, or access online at InfoResponse.

Cheers,

Kerry