none
SQL Server Express: Image Data Type max size RRS feed

  • Question

  • I've never worked with images in SQL databases before, and I'm having an issue with the data type size.  I designed my table in SQL Server Express, and I made the column type "image".  The documentation said it was supposed to be able to store up to 2G or something like that.  When I tried to upload the image from my .NET 2.0 web form, I got the message that it exceeded the sql_variant data type max size, which is 8000 bytes.  I tried making the data type nvarbinary(max), but SQL server express said that again the maximum data size is 8000 bytes.  Has anyone run into this problem and been able to fix it?  Thanks.

    Wednesday, August 29, 2007 8:54 PM

Answers

  • EDIT

     

    It could mean you did not create the database it maybe moved to 2005 from 2000 which means it cannot take Varbinary(max) because your database is in 80 compatibility mode which is 2000. So try creating a new database and add this table with the Management Studio table designer. And Go is not T-SQL try commenting out the Go.

     

    I almost forgot I noticed you are using SmallDateTime in a table definition that is not a good idea because of limited resolution you can only get to the minute with SmallDateTime.  It is generally used to trim duplicate time and if time is not needed.  Hope this helps.

     

     

    Wednesday, August 29, 2007 10:24 PM

All replies

  • The correct data type is Varbinary(max) and yes it let you upload large image to SQL Server.  Try the links below for details.


    http://msdn2.microsoft.com/en-us/library/ms188362.aspx

    http://msdn2.microsoft.com/en-us/library/ms173530.aspx

     

    Wednesday, August 29, 2007 9:22 PM
  • Thnk you for your answer.  Unfortunately, even when I select varbinary as the data type, I still get the message in design view that the length must be between 1 and 8000.  I tried going into the "edit" view and manually typing max into the length where 50 was, and I get a syntax error near 'max'.  When I try a larger number (e.g. 200000) I get the following message:

     

    The size (200000) given to the column 'TestSIGIMAGE' exceeds the maximum allowed for any data type (8000).

     

    Any ideas why this might be happening?  Is it specific to SQL Server Express?

     

     

    USE [xxxx]

    GO

    /****** Object: Table [xxxx].[xxxx] Script Date: 08/29/2007 16:45:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [xxxx].[SIGNATURES](

    [SIGNUM] [smallint] IDENTITY(1,1) NOT NULL,

    [SIGNAME] [ntext] NOT NULL,

    [SIGEDSSTEP] [nchar](1) NOT NULL,

    [SIGBIN] [tinyint] NOT NULL,

    [SIGDEVICE] [nchar](2) NULL,

    [SIGSTEPSEQ] [nvarchar](15) NULL,

    [SIGTOOL] [nvarchar](10) NULL,

    [SIGOWNER] [nchar](2) NULL,

    [SIGCREATED] [smalldatetime] NOT NULL,

    [SIGLASTUSED] [smalldatetime] NULL,

    [SIGIP1] [ntext] NULL,

    [SIGIP2] [ntext] NULL,

    [SIGIP3] [ntext] NULL,

    [SIGIP4] [ntext] NULL,

    [SIGCOUNTERMEASURE] [ntext] NULL,

    [SIGCOUNTERMEASUREDATE] [smalldatetime] NULL,

    [TestSIGIMAGE] [varbinary](max) NULL,

    CONSTRAINT [PK_SIGNATURES] PRIMARY KEY CLUSTERED

    (

    [SIGNUM] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

     

     

    Msg 170, Level 15, State 1, Line 18

    Line 18: Incorrect syntax near 'max'

    Wednesday, August 29, 2007 9:56 PM
  • EDIT

     

    It could mean you did not create the database it maybe moved to 2005 from 2000 which means it cannot take Varbinary(max) because your database is in 80 compatibility mode which is 2000. So try creating a new database and add this table with the Management Studio table designer. And Go is not T-SQL try commenting out the Go.

     

    I almost forgot I noticed you are using SmallDateTime in a table definition that is not a good idea because of limited resolution you can only get to the minute with SmallDateTime.  It is generally used to trim duplicate time and if time is not needed.  Hope this helps.

     

     

    Wednesday, August 29, 2007 10:24 PM
  • Thanks.  You were right about the database being created in 2000 (Compatibility mode 80).  I'm trying to get our IT guys to set a database up for me in 2005.

     

    I don't need resolution smaller than the minute for this application, which is why I used smalldatetime.  Thanks again.

    Thursday, August 30, 2007 2:47 PM