How to store file attachments (text and binary).

I need to store several different types of documents (text, MS Word, PDF, etc.) in SQL Server 2005. What is the best way to store file attachments that have different MIME types?

Currently I'm using a table with a varbinary(max) column which works for binary files but not for text files. Do I need to have multiple columns in the table for different file types? Thanks in advance for your help.

CREATE TABLE dbo.[CM_Attachment] (
[Id] int IDENTITY (1, 1) NOT NULL ,
[Change_Request_Id] int NOT NULL ,
[Attachment] varbinary(max) not null,
[Created_Date] datetime NOT NULL DEFAULT GetDate() ,
[Created_By] varchar(30) NOT NULL ,
[Modified_Date] datetime NOT NULL ,
[Modified_By] varchar(30) NOT NULL
)
GO

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB) -- Does not work
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob) -- Works
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob) -- Works
as ChangeRequestAttachment

I believe the datatype to use is "image" and it will store any file type.

Check out the link below:
http://www.aspnettutorials.com/tutorials/database/Save-Img-ToDB-Csharp.aspx

|||

2 quotes from "Microsoft SQL SERVER 2005 Implementation and maintenance Training Kit":

[

Binary Data Types

The most popular data type within this group is the image data type. This data type has an unfortunate name; it is not used exclusively to store images, such as a library of pictures from a recent vacation. Although you can store pictures in an image data type, you can also use this data type to store Word, Excel, PDF, and Visio documents. You can store any file that is 2 GB or less in size in an image data type. One of the most famous implementations of this data type is the TerraServer project, which is a multiterabyte database of terrestrial images that you can access at www.terraserver.com.

The varbinary(max) data type is new to SQL Server 2005. It can store the same amount of data as an image data type, and you can use it with all the operations and functions that you can use with binary/varbinary data types.

]

... and ...

[

Microsoft intends to drop support for the text, ntext, and image data types in upcoming SQL Server versions. For this reason, Microsoft recommends that you stop using these data types.

]

|||Can you please explain what you mean when you say it doesn't work for text? You would simply read it as SINGLE_BLOG and insert into varbinary(max) column. For text files, the data will be a binary stream so you should be able to simply cast it to varchar(max) or nvarchar(max) to read or see the text data. SQL Server doesn't care what you put inside varbinary(max) column or the nature of the content. The server simply sees the data as a stream of bytes.|||

Thank you everyone for your help thus far. Unfortunately I'm not quite there yet.

When trying to store a text file using SINGLE_BLOB my result is
(0 row(s) affected)

When trying to store a text file using SINGLE_CLOB my result is
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

SQL

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
--Results in the following:
--(0 row(s) affected)

-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
-- Results in this error:
-- Msg 257, Level 16, State 3, Line 1
-- Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

-- These two work.
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob)
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob)
as ChangeRequestAttachment

|||

I found my mistake! The text file I was testing with was a zero byte file (I forgot to put some text in the test file). Once I added some text to the test Text file the following two insert statements worked correctly.

-- This works using SINGLE_CLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, Convert(varbinary(max), BulkColumn) , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
as ChangeRequestAttachment

-- This works using SINGLE_BLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
as ChangeRequestAttachment

Thank you again everyone for your help; what a great resource this form is.