Laurence Neville
3/26/2007 2:28:00 PM
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???