JPGs added to Image column truncated at 1024 bytes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

JPGs added to Image column truncated at 1024 bytes

Author

Topic

lneville
Starting Member

12 Posts

Posted - 2007-03-26 : 10:34:16

I have a table in a SQL 2005 database that is designed to hold JPG product images for a website. Here is the schema:

create table dbo.Eur_RMISWebInterface_Staging_ProductImages(
product_code varchar(12) not null,
image_order smallint not null,
colour_identifier varchar(10) not null,
image_data image not null,
constraint PK_Eur_RMISWebInterface_Staging_ProductImages primary key clustered (
product_code,
image_order
)
)

Product images are inserted into the table from a database on a remote SQL 2000 server. Here is the script that does that (uses dynamic SQL):

set @SQLCmd = '
set xact_abort on

begin transaction

delete Eur_RMISWebInterface_Staging_ProductImages

insert Eur_RMISWebInterface_Staging_ProductImages (
product_code,
image_order,
colour_identifier,
image_data
)
exec "' + @RMISServerName + '".' + @RMISDatabaseName + '.dbo.Eur_RMISWebInterface_GetProductImagesForWeb ' + convert(varchar(3), @PortalID) + ', ' + @Locale_LCID + '

commit transaction'
execute (@SQLCmd)

About 500 images, each about 20KB, are transferred at a time. Here is the problem - when this script is run manually, all the images are inserted completely. When the script is run as part of a job (that has several other steps), the job step completes successfully and all the images are inserted, but every single one is truncated to the first 1024 bytes of the image. What this ends up looking like on the website is a a narrow strip of image instead of a complete image. Here are some other observations:

- When I changed the "Eur_RMISWebInterface_GetProductImagesForWeb" SP to only return 1 product image instead of 500 it still failed
- The owner of the job is the same Windows user as the user I have been running the script manually as
- I have tried changing the datatype of image_data from image to varbinary(max) but it made no difference

What could possibly be going on???

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-27 : 05:27:08

I think you need to detail "Eur_RMISWebInterface_GetProductImagesForWeb"