Post reply

JPGs added to Image column are truncated at 1024 bytes (but only when import executed as a Job)

  • March 26, 2007 at 8:35 am

    #172595

    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

      )

    &nbsp

    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???

  • April 6, 2009 at 2:21 am

    #972344

    What does the dbo.Eur_RMISWebInterface_GetProductImagesForWeb procedure do?

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply