title: Export Blob From SQL Server and save it as a file
body: <p>I am trying to Export Data from SQL Server to a file. The data is docs, pdf, jpegs, xls, and sms. I found a script that would do so however whenever i run it I receive a non descriptive error message </p>
  
  <p>C:\ExtractBlob\folder1\folder2\folder3
  Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 30 [Batch Start Line 8]</p>
  
  <p>My code is below: </p>
  
  <p><strong>Procedure to create a folder to store Blobs in</strong></p>
  
  <pre><code>            sp_configure 'show advanced options', 1;
              GO
              RECONFIGURE;
              GO
              sp_configure 'Ole Automation Procedures', 1;
              GO
              RECONFIGURE;
              GO
  
              CREATE PROCEDURE [dbo].[CreateFolder] (@newfolder varchar(1000)) AS 
              BEGIN 
              DECLARE @OLEfolder   INT 
              DECLARE @OLEsource   VARCHAR(255) 
              DECLARE @OLEdescription  VARCHAR(255)
              DECLARE @init   INT 
              DECLARE @OLEfilesytemobject INT
  
              -- it will fail if OLE automation not enabled
              EXEC @init=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT 
              IF @init &lt;&gt; 0 
              BEGIN 
                  EXEC sp_OAGetErrorInfo @OLEfilesytemobject 
                  RETURN 
              END 
              -- check if folder exists
              EXEC @init=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @newfolder
              -- if folder doesnt exist, create it 
                  IF @OLEfolder=0 
                  BEGIN 
                  EXEC @init=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @newfolder 
              END 
              -- in case of error, raise it  
              IF @init &lt;&gt; 0 
                  BEGIN 
                      EXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT 
                      SELECT @OLEdescription='Could not create folder: ' + @OLEdescription 
                      RAISERROR (@OLEdescription, 16, 1)  
                  END 
              EXECUTE @init = sp_OADestroy @OLEfilesytemobject 
              END 
  </code></pre>
  
  <p><strong>Export Code</strong></p>
  
  <pre><code>            USE [IQS]
              DECLARE @outPutPath varchar(50) = 'C:\Users\trenton.gibbs\Documents\Extract'
              , @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 @Imagetable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
  
              INSERT INTO @Imagetable([Doc_Num],[FileName],[Doc_Content])
              Select [Link_Embed_Sysid],[File_Location], Convert(varbinary(max),[Embed_Object]) FROM  [dbo].[Link_Embed]
              Where Create_Date Between '9/1/2018' And '9/8/2018'
              And [Embed_Object] IS NOT NULL
  
              --SELECT * FROM @Imagetable
  
              SELECT @i = COUNT(1) FROM @Imagetable
  
              WHILE @i &gt;= 1
              BEGIN
  
                  SELECT
                   @data = [Doc_Content],
                   @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
                   @folderPath = @outPutPath + '\'+ [Doc_Num]
                  FROM @Imagetable WHERE id = @i
  
                --Create folder first
                EXEC  [dbo].[CreateFolder]  @folderPath
  
                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
  </code></pre>
  
  <p>Does anyone have any idea what could be causing this error?</p>