SQL Server Performance Forum – Threads Archive
JPGs added to Image column truncated at 1024 bytes
I have a table in a SQL 2005 database that is designed to hold JPG productimages 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??? [?]
Try to specify the value as Varbinary (max) using cast/convert function… Ex:
set nocount on
create table demo (ID int identity, LargeString varchar(max))
insert into demo (LargeString) values (‘abc’ + replicate(‘d’, 100000) + ‘efg’)
insert into demo (LargeString) values ( ‘abc’ + replicate(cast(‘d’ as varchar(max)), 10000) + ‘efg’)
select ID, len(LargeString) Length from demo
drop table demo MohammedU.
Moderator
SQL-Server-Performance.com
Just a note on the data type IMAGE. Since you are using SQL Server 2005, you should use VARBINARY(MAX) instead. IMAGE is deprecated and will be removed in a future version. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
I figured it out. The answer was SET TEXT SIZE. I added the following before the Insert and the images are now complete: set textsize 1000000 I also switched to VARBINARY(MAX) as recommended.
]]>