I appreciate the tip, but there are restrictions to the filesystem access, and our client / server application only has read write access to the database. Any help with storing in the database would be greatly appreciated.
We are doing this because our application has VBA embedded in it. In order to serve up a macro driven event, we use VBA event handling. Also, the user will not have access to the file system, but will have access to our client server application. Therefore, we have a need to store a few binary objects through VBA, to a MS SQL 2005. Any help is much appreciated.
Method 1: Save the PDF files as Binary Large Objects (BLOBs) in SQL Server
The first method is to store the PDF file, in a BLOB data type column, in the database. “A Binary Large Object (or BLOB) is a collection of binary data stored as a single entity in a database management system. BLOBs are typically images, audio or other multimedia objects, though sometimes binary code is stored as a BLOB.” (wikipedia)
Method 2: Save the PDF files to a drive and assign a pointer from SQL Server to the location of the PDF file.
The second method is to save the PDF file to a drive and then save the location of the PDF in the database.
Method 1 Size - Larger Performance - Slower Backup - SQL Server will backup all the documents every time a full database backup is created. Security - SQL Server will handle security Possibility of getting out of sync - Lower Expanding Data Files - Harder Full Text Search*** - Available with 3rd party application, but slower performance if selected.
Method 2 Size - Smaller Performance - Faster Backup - A separate job will have to be created to backup the PDF files. Security - Permissions will have to be given to the folder to specific users, user groups, or application accounts. Possibility of getting out of sync - Higher Expanding Data Files - Easier Full Text Search*** - Available with 3rd party application, but slower performance if selected.
*** Full text search refers to a technique for searching a text fields in a SQL Server database; in a full text search, the database examines all of the words in every text field as it tries to match search words supplied by the user. The 3rd party application will extract the text out of the PDF file and save it to a text field in the database. The text field will then be searched. The original PDF file will be unchanged.