You can use several methods to insert files (pdf and other file types) to be stored in SQL Server table that includes a column defined with the IMAGE data type. You can use BCP.exe (Bulk Copy Program), BULK INSERT, TextCopy.exe as well as ADO and other programming languages, below are some KB articles that describe these methods as well as SQL code examples. Note, more details on BCP and BULK INSERT can be found in SQL Server 2000 Books Online (BOL).

309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#

http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158

308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with VB.NET

http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042

326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET

http://support.microsoft.com/?id=326502

-- BCP IN file using xp_cmdshell

exec master..xp_cmdshell 'bcp pubs..authors_copy in d:\authors.txt -Usa -P -c'

-- Texcopy -I (in) using xp_cmdshell

exec master..xp_cmdshell 'textcopy -I -Usa -P -Sjtk0 -Dpubs -Tpub_info_copy -Clogo -W"where pub_id = ''0736''" -FD:\MSSQL70\Install\algodata.gif'

-- BULK INSERT using variable file name in a stored procedure

CREATE PROC Sp_EnterTextFile @filename sysname

as

BEGIN

   SET nocount ON

   CREATE TABLE #tempf (line varchar(8000))

   EXEC ('bulk INSERT #tempf FROM "' + @filename + '"')

   SELECT * FROM #tempf

   DROP TABLE #tempf

END

GO

These are several methods to insert binary files into an column defined with the IMAGE datatype. Note, there are people who would argue that you need only store the path to the file's location on the server disk drive, but I am not among those people who agree with this argument, primarly becasue when you insert the PDF files and use the free Adobe PDF IFilter, you can use the SQL Server 2000 Full-text Search (FTS) feature to search on the contents of the pdf file using CONTAINS and FREETEXT. You cannot use FTS, if you only store the path to the file on disk...

Thanks,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/