January 24, 2007 at 7:06 am
We are looking at implementing a new table that will have PDFs inserted into a column of data type image, Is there a way to limit the size of the incoming file in SQL? i.e. we only want to allow up to a 15MB file.
Thanks
David Smerchek
January 24, 2007 at 1:04 pm
I have not tested it but I guess you could use the normal string functions to determine the length of the input string (in your insert stored procedure for example).
Personally I think this kind of logic should be in the application however. But that's a matter of opinion of course.
January 25, 2007 at 2:15 am
Personally...I'd also keep the PDF's out of SQL, and just store references to their actual LAN location. Putting them in SQL generates a large image management issue into SQL (backup/restore/read in/update), putting them on the LAN as individual files generates a synchronisation issue in the event of images being deleted and/or database being moved/restored.
It's a tradeoff of problems...but the latter seems to be smaller than the former.
January 25, 2007 at 11:04 am
Thanks for the information, keeping just a reference in the database I think would be the best. But the developers are pushing to have the PDF inside SQL so they do not need to store the files anywhere else, and the database team worry about the backups and restore.
January 25, 2007 at 11:31 am
If you want to use use VARBINARY(MAX)...
I believe IMAGE datatype is depricated in 2005...
MohammedU
Microsoft SQL Server MVP
January 25, 2007 at 12:07 pm
I checked the SQL Server 2005 "books online", and it appears the IMAGE datatype is not deprecated. It holds a maximum of 2 Gb per entry. I'm not sure if there is a SQL Server method to directly limit the size to 15 Mb, but either application language code or T-SQL could be written to check the PDF file size to enforce the limit.
Having said this, and having worked in the development and maintenance of imaging systems for some time now, I'd offer the opinion that the application developers are really making a poor design choice, wanting to store image files within the database. System design is supposed to be a TEAM effort -- and the developers should be willing to at least entertain alternative design proposals from the DBA.
January 25, 2007 at 5:01 pm
Im in favor of images and similar things in the db. It's not as bad as most think. As just for fun, ever try to open a folder that has 100k files using Explorer?
I do agree that alternative proposals should be discussed, and in this case there are a quite a few things to weigh.
January 26, 2007 at 12:26 am
image is deprecating, meaning it will probably be removed in a future version. So I would indeed use varbinary(max).
Although I agree that storing them on the filesystem is an option that should be looked at too.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply