Here is the SP that I have developed to extract the files from database to local folder.
ALTER PROCEDURE [Z_EXTRACT_EACE_DOCUMENTS]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @V_PATH VARCHAR(200)
,@V_OPATH VARCHAR(200)
,@OBJECTTOKEN INT
,@V_DOCUMENT_ID NUMERIC
,@V_FILENAME VARCHAR(200)
,@V_FILE VARBINARY(MAX)
,@V_DIRECTORY VARCHAR(200)
,@V_FOLDER_RESULT INT
,@V_FILE_RESULT INT
,@V_Folder_PATH VARCHAR(200)
,@V_FILE_PATH VARCHAR(200)
,@V_FOLDER_CHECK INT
/* Fetch file details to cursor */
BEGIN
DECLARE CR_ACE_DOC CURSOR
FOR
SELECT attachmentID,filename,filedata
FROM dbo.tblAttachments ace
WHERE ace.attachmentID NOT IN (
SELECT attachment_id
FROM dbo.z_file_extract_status
WHERE attachment_id = ace.attachmentid
AND file_cerated = 1
)
END
/* Open cursor */
BEGIN
OPEN CR_ACE_DOC
FETCH NEXT
FROM CR_ACE_DOC
INTO @V_DOCUMENT_ID
,@V_FILENAME
,@V_FILE
WHILE @@FETCH_STATUS = 0
BEGIN
/* Create directory to extract eace attachment */
SET @V_PATH = '\\clarity01\docs\' + CONVERT(VARCHAR, @V_DOCUMENT_ID)
SET @V_DIRECTORY = 'mkdir ' + @V_PATH
/* Assign folder creation status to field */
EXEC @V_FOLDER_RESULT = master..xp_cmdshell @V_DIRECTORY, no_output
/* check if folder is already created */
SET @V_FOLDER_PATH = 'dir ' + @V_PATH + ' /B'
EXEC @V_FOLDER_CHECK = master..xp_cmdshell @V_FOLDER_PATH, no_output
IF (@V_FOLDER_RESULT = 0 or @V_FOLDER_CHECK = 0 )
BEGIN
IF (@V_FOLDER_CHECK = 0)
BEGIN
/* delete record in log table if folder is already created and dont had files */
delete from z_file_extract_status where attachment_id = @V_DOCUMENT_ID
END
/* insert into log table */
INSERT INTO z_file_extract_status (attachment_id,folder_created,file_cerated,Comments)
VALUES (@V_DOCUMENT_ID,1,NULL,'Folder created Successfully')
/* Extract the document to newly created folder */
SET @V_OPATH = @V_PATH + '\' + @V_FILENAME
EXEC sp_OACreate 'ADODB.Stream',@ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken,'Type',1
EXEC sp_OAMethod @ObjectToken,'Open'
EXEC sp_OAMethod @ObjectToken,'Write',NULL,@V_FILE
EXEC @V_FILE_RESULT = sp_OAMethod @ObjectToken,'SaveToFile',NULL,@V_OPATH,2
EXEC sp_OAMethod @ObjectToken,'Close'
EXEC sp_OADestroy @ObjectToken
IF (@V_FILE_RESULT = 0)
BEGIN
/* update log table on file export */
update z_file_extract_status set folder_created = 1
,file_cerated = 1
,Comments = 'Folder files are exported Successfully'
where attachment_id = @V_DOCUMENT_ID
END
ELSE
BEGIN
/* insert into log table */
update z_file_extract_status set folder_created = 1
,file_cerated = 0
,Comments = 'Folder created but files creation failed'
where attachment_id = @V_DOCUMENT_ID
END
END
ELSE
BEGIN
/* insert into log table */
INSERT INTO z_file_extract_status (attachment_id,folder_created,file_cerated,Comments)
VALUES (@V_DOCUMENT_ID,0,NULL,'Folder creation failed')
END
FETCH NEXT
FROM CR_ACE_DOC
INTO @V_DOCUMENT_ID
,@V_FILENAME
,@V_FILE
END
/* Close and Deallocate cursor */
CLOSE CR_ACE_DOC
DEALLOCATE CR_ACE_DOC
END
SET NOCOUNT OFF;
END
Regards,
Shiva