Issue:
A customer would like to be able to extract documents that are stored in the SedonaDocuments database.
How can this be done?
Resolution:
Open SQL Server Management Studio and connect to the SQL server where the SedonaDocuments database is located.
Open a new query window.
You can do this by right clicking on the SedonaDocuments database and select New Query
You can also click the New Query button in the toolbar.
Verify the SedonaDocuments database is selected in the drop-down menu.
The script below will allow you to export all the attached files that are stored in the table.
--- Turn on the 'Ole Automation Procedures ---
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
--- Set the @outPutPath to the folder where documents will be saved. ---
DECLARE @outPutPath varchar(50) = 'C:\SO_Documents'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath varchar(max)
--Get Data into temp Table variable so that we can iterate over it ---
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num] varchar(100) , [FileName] varchar(100), [Doc_Content] varBinary(max) )
--- Remove the -- from the front of the table name where the docuemnts have been saved. ---
--- This will extract all docuemnts from the individual table. ---
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [Document_Id] , [FileName],[FileData] FROM
-- AP_Vendor_Documents
AR_Customer_Documents
-- AR_Deposit_Check_Documents
-- IN_Part_Documents
-- SM_Prospect_Documents
-- SS_General_Documents
-- SV_Inspection_Report
-- SV_Service_Ticket_Documents
-- SY_Employee_Documents
SELECT @i = COUNT(1) FROM @Doctable
WHILE @i >= 1
BEGIN
SELECT
@data = [Doc_Content],
@fPath = @outPutPath + '\' + [Doc_Num] +'_' +[FileName],
@folderPath = @outPutPath + '\'+ [Doc_Num]
FROM @Doctable WHERE id = @i
EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1;
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources
print 'Document Generated at - '+ @fPath
--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
END
