iVr116Wp

1sp_configure 'show advanced options', 1;
2            GO
3            RECONFIGURE;
4            GO
5            sp_configure 'Ole Automation Procedures', 1;
6            GO
7            RECONFIGURE;
8            GO
9
10            CREATE PROCEDURE [dbo].[CreateFolder] (@newfolder varchar(1000)) AS 
11            BEGIN 
12            DECLARE @OLEfolder   INT 
13            DECLARE @OLEsource   VARCHAR(255) 
14            DECLARE @OLEdescription  VARCHAR(255)
15            DECLARE @init   INT 
16            DECLARE @OLEfilesytemobject INT
17
18            -- it will fail if OLE automation not enabled
19            EXEC @init=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT 
20            IF @init <> 0 
21            BEGIN 
22                EXEC sp_OAGetErrorInfo @OLEfilesytemobject 
23                RETURN 
24            END 
25            -- check if folder exists
26            EXEC @init=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @newfolder
27            -- if folder doesnt exist, create it 
28                IF @OLEfolder=0 
29                BEGIN 
30                EXEC @init=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @newfolder 
31            END 
32            -- in case of error, raise it  
33            IF @init <> 0 
34                BEGIN 
35                    EXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT 
36                    SELECT @OLEdescription='Could not create folder: ' + @OLEdescription 
37                    RAISERROR (@OLEdescription, 16, 1)  
38                END 
39            EXECUTE @init = sp_OADestroy @OLEfilesytemobject 
40            END
41	
42USE [IQS]
43            DECLARE @outPutPath varchar(50) = 'C:Userstrenton.gibbsDocumentsExtract'
44            , @i bigint
45            , @init int
46            , @data varbinary(max)
47            , @fPath varchar(max) 
48            , @folderPath  varchar(max)
49
50            --Get Data into temp Table variable so that we can iterate over it
51            DECLARE @Imagetable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
52
53            INSERT INTO @Imagetable([Doc_Num],[FileName],[Doc_Content])
54            Select [Link_Embed_Sysid],[File_Location], Convert(varbinary(max),[Embed_Object]) FROM  [dbo].[Link_Embed]
55            Where Create_Date Between '9/1/2018' And '9/8/2018'
56            And [Embed_Object] IS NOT NULL
57
58            --SELECT * FROM @Imagetable
59
60            SELECT @i = COUNT(1) FROM @Imagetable
61
62            WHILE @i >= 1
63            BEGIN
64
65                SELECT
66                 @data = [Doc_Content],
67                 @fPath = @outPutPath + ''+ [Doc_Num] + '' +[FileName],
68                 @folderPath = @outPutPath + ''+ [Doc_Num]
69                FROM @Imagetable WHERE id = @i
70
71              --Create folder first
72              EXEC  [dbo].[CreateFolder]  @folderPath
73
74              EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
75              EXEC sp_OASetProperty @init, 'Type', 1; 
76              EXEC sp_OAMethod @init, 'Open'; -- Calling a method
77              EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
78              EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
79              EXEC sp_OAMethod @init, 'Close'; -- Calling a method
80              EXEC sp_OADestroy @init; -- Closed the resources
81
82              print 'Document Generated at - '+  @fPath  
83
84            --Reset the variables for next use
85            SELECT @data = NULL 
86            , @init = NULL
87            , @fPath = NULL 
88            , @folderPath = NULL
89            SET @i -= 1
90            END