Storing Documents w/ SQL

[smbarney]

I am using Access 2003 as a front-end with SQL Server 2005 for a back-end. I need to store documents (pdf, jpg, doc, etc). How do I do it with SQL from access? I would like to store the documents SQL and not as a reference. Thanks.

 

[SQL_Hell]

Hi there,

You wont find a huge amount of information about this simply because it really isn't the best thing to do, you are much more likely to program in performance issues into your database by storing the images in the database rather than a link to the file.

If the image is larger than 8k (which most are ofc) then sql server needs to break the image up into multiple 8K chunks these chunks will be stored on separate sql server pages. The process of breaking images into 8k chunks and then reassembling the chunks causes performance issues and the bigger the images the worse the problem gets.

But if you really have to do this and have no other option then sql server provides an executable for this kinda of thing called TEXTCOPY

TEXTCOPY [/S [sqlserver] ] [/U [login] ] [/P [password] ]
  [/D [database] ] [/T table] [/C column] [/W"where clause"]
  [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

  /S sqlserver       The SQL Server to connect to. If 'sqlserver' is not
                     specified, the local SQL Server is used.
  /U login           The login to connect with. If 'login' is not specified,
                     a trusted connection will be used.
  /P password        The password for 'login'. If 'password' is not
                     specified, a NULL password will be used.
  /D database        The database that contains the table with the text or
                     image data. If 'database' is not specified, the default
                     database of 'login' is used.
  /T table           The table that contains the text or image value.
  /C column          The text or image column of 'table'.
  /W "where clause"  A complete where clause (including the WHERE keyword)
                     that specifies a single row of 'table'.
  /F file            The file name.
  /I                 Copy text or image value into SQL Server from 'file'.
  /O                 Copy text or image value out of SQL Server into 'file'.
  /K chunksize       Size of the data transfer buffer in bytes. Minimum
                     value is 1024 bytes, default value is 4096 bytes.
  /Z                 Display debug information while running.
  /?                Display this usage information and exit.

Syntax example:

C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TEXTCOPY.exe" /S(local)
/U sa /P bfdgfsd /D prod /T Images /C file /F "C:\racer.jpg"
/W"where Title='Lewis Hamilton'" /I

Can be executed in DOS (batch file) or via EXEC XP_CMDSHELL

 

[smbarney]

I hadn't considered the 8k chuncks. How would I do it as a link to a file? More importantly, how would I upload the file, provide access to it for the user, and delete it if needs be while maintaining some level of security? I am using Access 2003 as the front-end.

I’ve do this sort of thing in Access. However, the problem is that you must do it to a file share that everyone has read/write access which provides no level of security. How can I make it so the reference link is accessible by SQL and viewable from Access?

Thanks

 

[SQL_Hell]

I think storing the images as a link in SQL Server is pretty much the same as it is in access in terms of displaying on a form and yes I beleive the images will have to be put on some file share.

I suppose if you don't display the path to the users even though they all have access to access / delete then that would provide somekind of protection because they wont know where to look.

another option is to have somekind of process to copy the file down to the users machine ready to be displayed, but this could be tricky to implement.

You could consider not allowing the users to delete images at all and have somekind of automated sql server system process to do this because presumably the record will also need to be updated if the image is no longer there.

I cant think of any other way round at the moment, but I will continue thinking about it