Image data type and publication problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Image data type and publication problem

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
What is the level of SQL & Service pack on both the machines? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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

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