Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: image datatype in SQL 2000 to varbinary(max) in SQL 2005

    We used SSIS to move data from a table in SQL 2000 which had a column with the image datatype to a column in a table in SQL 2005 that has a datatype of varbinary(max). No errors were produced from the SSIS package.

    There were a number of records where the DATALENGTH of the column with the image datatype was greater than 8000. Was the data truncated for these records?

    This is probably a very elementary question, but I am not familiar with the application or the data.

    Below is the source table in SQL 2000 and a select count(*) ...

    Code:
    CREATE TABLE [dbo].[LSCHANNELCUTDATA](
    	[UIDCHANNELCUT] [numeric](19, 0) NOT NULL,
    	[VALUECODES] [image] NULL,
    ...
    Code:
    select	count(*)
    from	[LSCHANNELCUTDATA]
    where	DATALENGTH ( [VALUECODES] )	> 8000

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    As long as the datalength returns the value less or equal to 2^31-1 bytes, you'll be fine.

    BOL:
    Use varbinary(max) when the column data entries exceed 8,000 bytes.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    thanks ... you are correct ...

    when I hover the cursor over the storage type of varbinary under the Programmability tree in Management Studio 8000 is shown as the max length ... which is the max length if you specify a length n instead of specifying "max".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •