Hi Martina,

I found the solution, apart from setting access to file system I found also an error in the code:

@outPutPath + '\'+ RTRIM([Doc_Num]) + '\' + RTRIM([FileName]),

would create a path with a subfolder that does not exist and therefore fails. So I changed it into

@outPutPath + '\'+ RTRIM([FileName]) + '.pdf',

and this works perfectly.

>Hi Christian,
>
>

> DECLARE @liErr INT, @lcSource VARCHAR(4000), @lcDescription VARCHAR(4000)
>
>EXEC @liErr = sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
>EXEC @liErr = sp_OASetProperty @init, 'Type', 1; 
>EXEC @liErr = sp_OAMethod @init, 'Open'; -- Calling a method
>EXEC @liErr = sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
>EXEC @liErr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
>IF @liErr<>0 BEGIN
>   EXEC @liErr = sp_OAGetErrorInfo @loHTTP , @lcSource OUTPUT, @lcDescription OUTPUT
>   PRINT @lcSource
>   PRINT @lcDescription
>END
>EXEC @liErr = sp_OAMethod @init, 'Close'; -- Calling a method
>EXEC @liErr = sp_OADestroy @init; -- Closed the resources
>

>
>Of course, MSSQL must has access to file system.
>
>MartinaJ
>>I have the following procedure to create files from a varchar field, but although it seems to run successfully, it does not produce the files. I hope someone can point me into the right direction what the reason for this issue could be:
>>
>>

>>RECONFIGURE; 
>>GO 
>>sp_configure 'Ole Automation Procedures', 1 
>>GO 
>>RECONFIGURE; 
>>GO 
>>sp_configure 'show advanced options', 1 
>>GO 
>>RECONFIGURE;
>>
>>
>>DECLARE @outPutPath varchar(50) = 'C:\Files\Export'
>>, @i bigint
>>, @init int
>>, @data varchar(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] varchar(max))
>>
>>INSERT INTO @Doctable([Doc_Num], [FileName], [Doc_Content])
>>              SELECT Employees.emNumber
>>              , Employees.emNumber
>>              , SavedPayslips.saFile
>>       FROM Employees
>>       JOIN PayEmps ON PayEmps.paEmKey = Employees.emKey
>>       JOIN Runs ON Runs.ruKey = PayEmps.paRuKey
>>       JOIN SavedPayslips ON SavedPayslips.saEmKey = Employees.emKey
>>              AND SavedPayslips.saRuKey = Runs.ruKey
>>
>>--SELECT * FROM @table
>>
>>SELECT @i = COUNT(1) FROM @Doctable
>>
>>WHILE @i >= 1
>>
>>BEGIN
>>
>>SELECT
>>     @data = [Doc_Content],
>>     @fPath = @outPutPath + '\'+ RTRIM([Doc_Num]) + '\' + RTRIM([FileName]),
>>     @folderPath = @outPutPath + '\'+ RTRIM([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
>>