Solved

Saving RTF file in table

Posted on 2012-03-23
5
724 Views
Last Modified: 2012-03-26
Can you save an RTF file in a MS SQL table?  Is an image field type exclusively designed for image?
0
Comment
Question by:TSFLLC
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 37756451
You could possibly look into using FILESTREAM

You can create a varbinary(max) column for filestream and then BULKINSERT a document into this. It uses the FileSystem
You can create a full text index on this column which allows you to use full-text predicates to query the document

CREATE TABLE [dbo].[Items](

   [ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

   [ItemNumber] VARCHAR(20),

   [ItemDescription] VARCHAR(50),

   [ItemImage] VARBINARY(MAX) FILESTREAM NULL

)

)

http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/
0
 

Author Comment

by:TSFLLC
ID: 37756552
Very interesting.  More detailed in it's initial requirements to accomplish than expected but if it's fail safe it will be worthwhile.  Will review and test over the weekend.  Thanks for the speedy post.
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 37756556
When to Use FILESTREAM
In SQL Server, BLOBs can be standard varbinary(max) objects (data is stored in tables) or FILESTREAM varbinary(max) objects (data is stored in the file system). The size and use of the data determines whether you should use database storage or file system storage.
If any of the following conditions are true, you should consider using FILESTREAM:
•      The objects to be stored are, on average, larger than 1 megabyte (MB).
•      Fast read access is important.
•      You are developing applications that use a middle tier for application logic.
You should generally store BLOBs smaller than 256 kilobytes (KB) inside the database, and store BLOBs larger than 1 MB outside the database. For BLOBs sized between 256 KB and 1 MB, the more efficient storage solution depends on the read:write ratio of the data, and on the rate of “overwrite” . Generally, storing them as varbinary(max) BLOBs in the database provides better streaming performance than storing them outside of the database
Use these considerations as a starting point for deciding if BLOBs should be stored outside of the database. If you plan to store the BLOBs outside the database, you can then evaluate whether RBS or FILESTREAM is the most appropriate solution.
For more information, see Chapter 7 (“Special Storage”) of Microsoft SQL Server 2008 Internals . This chapter provides points to consider when comparing in-database or file system storage. While not required for implementing FILESTREAM, the discussion in the book is useful when selecting the most efficient storage medium based on the BLOB size and impact to database management operations.
0
 

Author Comment

by:TSFLLC
ID: 37756679
Thanks.

For additional understanding...I have an 'Email' button added to a Crystal Report Viewer form.  Based on the type of report being generated, I'm merge (2) RTF files and put it in the body of the email.  RTF #1 includes generic text associated with this report.  RTF #2 is a signature file stored locally by the user generating the report/email.

Therefore RTF #1 can be created/edited by a user on ANOTHER computer and is stored on the server for all users to take advantage of, whereas the signature RTF is created by a user and stored locally in my app path.  I don't want my app to be required to pull the RTF #1 file across the network every time.  A particular report with email generation will be performed 100's of times each day.

By including in a SQL table, I'm not having to maintain an app path to point to RTF #1.  All I have to do is select from an already connected database.

Sound appropriate?
0
 

Author Closing Comment

by:TSFLLC
ID: 37766215
BCunney, the deciding factor is definitely the file size.  In this case, varbinary(MAX)  stored in the table is the proper design.  In the event I need to use FILESTREAM I have the necessary instructions.  Thanks much.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now