Extract Binary *.doc Files from a table

• mmuhlenkort2000

  Grasshopper

  Points: 15

  More actions

  October 18, 2010 at 5:59 am

  #141190

  We have several thousand documents stored in a customized application which need to be extracted to move to a more robust enterprize content management system. In order to accomplish this I need to extract these documents to a folder then upload to the new system. I've see articles on textcopy but that copies single documents. I need to automate the process to extract all the items from the table.

  Any help or guidance is greatly appreciated.

  Mark

• Lowell

  SSC Guru

  Points: 323547

  More actions

  October 18, 2010 at 6:08 am

  #1238347

  since you mentioned textcopy, here's pair of procedures to help you use it a little easier. I had this saved in my snippets.

  In TSQL, you will have to loop thru your table and process each image one at a time.

  I think it's easier in a programming language, but both can get the job done.

  --**************************************

  --

  -- Name: Procedure to Export/Import Imag

  -- es In/From SQL SERVER

  -- Description:the procedure is used as

  -- an Interface to the TextCopy Utility sup

  -- plied with SQL SERVER in order to simpli

  -- fy handling of BLOBS (images/docs)

  -- By: Eli Leiba

  --

  -- Inputs:@runpath varchar(100), -- text

  -- Copy Location

  @srvrvarchar(50), -- server TO LOAD

  @db varchar(50), -- DATABASE TO LOAD

  @usrvarchar(50), -- login USER

  @pwdvarchar(50), -- login password

  @tblvarchar(50), -- TABLE TO load/unload

  @colvarchar(50), -- COLUMN TO load/unload

  @whrvarchar(200), -- WHERE clause

  @filvarchar(100), -- filename including path

  @modchar(1) -- I FOR LOAD INTO Sql

  --

  -- Assumes:-- usage (assuming TextCopy.e

  -- xe is in c:\)

  CREATE TABLE pic (pic_id int,picture image)

  INSERT INTO pic VALUES (1,null)

  UPDATE pic SET picture = 'xx'

  -- Inserting image

  EXEC sp_imp_exp_images 'c:\textCopy.exe',

  'PCN1943',

  'PUBS',

  'sa',

  'NotARealPassword',

  'pic',

  'picture',

  '"where pic_id = 1"',

  'c:\pic.jpg',

  'I'

  -- Extracting Image

  EXEC sp_imp_exp_images 'c:\textCopy.exe',

  'PCN1943',

  'PUBS',

  'sa',

  'NotARealPassword',

  'pic',

  'picture',

  '"where pic_id = 1"',

  'D:\pic.jpg',

  'O'

  --

  -- Side Effects:Import/Export of BLOBS

  --

  --This code is copyrighted and has-- limited warranties.Please see http://

  -- www.1SQLStreet.com/xq/ASP/txtCodeId.498/

  -- lngWId.5/qx/vb/scripts/ShowCode.htm--for details.--**************************************

  --

  CREATE PROCEDURE sp_imp_exp_images

  (@runpath varchar(100), -- textCopy Location

  @srvrvarchar(50), -- server TO LOAD

  @db varchar(50), -- DATABASE TO LOAD

  @usrvarchar(50), -- login USER

  @pwdvarchar(50), -- login password

  @tblvarchar(50), -- TABLE TO load/unload

  @colvarchar(50), -- COLUMN TO load/unload

  @whrvarchar(200), -- WHERE clause

  @filvarchar(100), -- filename including path

  @modchar(1)) -- I FOR LOAD INTO Sql , O FOR output FROM SQL

  AS

  DECLARE @cmd varchar(1000)

  SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +

  ' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +

  ' /F ' + @fil+ ' /' + @mod

  EXEC Master..xp_cmdShell @cmd

  GO

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!