SQL Server Performance

Image data type and publication problem

Discussion in 'Performance Tuning for SQL Server Replication' started by aghabagheri, Sep 7, 2003.

  1. aghabagheri New Member

    Hello there,

    I have set up a publisher and ditributor on server1. an article in publication is a Pictures table that consists of Image data type column.

    that's ok.

    I' ve developed a data entry interface.

    but when I try to insert a new 4KB or more image via that interface, I encouter with an error, namely "unspecified error occurred".

    I can insert only images with 3KB or less sizes.

    what shall I do?

    Thanks in advance for your help,
    Ali Aghabagheri
  2. satya Moderator

    What is the level of SQL & Service pack on both the machines?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. aghabagheri New Member

    Hello Satya SKJ,

    Both Server specification:
    SQL Server 2000 and Service pack 3.

    I have only problem in inserting a picture in published table via my interface.

    There is no problem when removing that article from publication.(i.e. picture table)

    Thanks,
    Ali Aghabagheri
  4. Twan New Member

    Are you using the text in row option on the table? If so, then beware that replication row size restrictions are more severe than 8000 bytes for the entire row...?

    What type of replication are we talking about and what is the complete table definition?

    Cheers
    Twan
  5. aghabagheri New Member

    I try both snapshot and transactional replication but did not succeed.<br /><br />The error message that appeared when I inset a Picture:<br /><br />Length of text, ntext, or image data (10086<img src='/community/emoticons/emotion-11.gif' alt='8)' /> to be replicated exceeds configured maximum 65536.<br />The statement has been terminated.<br /><br /><br />Table Schema is:<br /><br />CREATE TABLE [Pictures] (<br />[PictureID] [uniqueidentifier] NOT NULL ,<br />[CameramanID] [udtArchiveNo] NULL ,<br />[FilmID] [udtArchiveNo] NULL ,<br />[MainImageOfFilm] [udtBoolean] NULL ,<br />[SiteUserID] [udtSiteUserID] NULL ,<br />[MainImageOfSiteUser] [udtBoolean] NULL ,<br />[Graphic] [image] NOT NULL ,<br />[Offstage] [udtBoolean] NULL ,<br />[Poster] [udtBoolean] NULL ,<br />[FaDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[EnDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[FrDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[RuDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[ArDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />CONSTRAINT [PK_FaPictures_ID] PRIMARY KEY CLUSTERED <br />(<br />[PictureID]<br />) WITH FILLFACTOR = 90 ON [PRIMARY] ,<br />CONSTRAINT [FK_Cameraman_Pictures_FaPeople] FOREIGN KEY <br />(<br />[CameramanID]<br />) REFERENCES [Fa_People] (<br />[PeopleID]<br />),<br />CONSTRAINT [FK_Pictures_FaFilms] FOREIGN KEY <br />(<br />[FilmID]<br />) REFERENCES [Fa_Films] (<br />[FilmID]<br />) ON UPDATE CASCADE ,<br />CONSTRAINT [FK_Pictures_SiteUsers] FOREIGN KEY <br />(<br />[SiteUserID]<br />) REFERENCES [SiteUsers] (<br />[SiteUserID]<br />) ON DELETE CASCADE ON UPDATE CASCADE <br />) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]<br />GO<br /><br />thanks,<br />Ali Aghabagheri<br /><br /><br />
  6. Twan New Member

    It is saying that you have an image field which is 100868 bytes long...

    You can use the following to increase this up to 2GB


    sp_configure 'max text repl size'

    Cheers
    Twan
  7. aghabagheri New Member

    Hello Twan


    Your answer is completely correct.
    I want to know if there is another alternative option to do replication for image data type.


    Thanks
    ==========
    Ali Aghabagheri
  8. satya Moderator

    As Twan specified other than using the 'max text repl size' option I haven't seen any other alternative.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. Twan New Member

    Hi there,

    as Satya says there is no alternative if you want to use SQL Replication...

    What are the images...? A SQL database is typically not the best place for this large BLOB type data. If the image is strictly a jpg or tiff image which the application writes to a file before using it, then I'd consider storing the images as flat files and store a directory reference to them in SQL...

    Cheers
    Twan

Share This Page