Export System files (DomainSecurity.xml and such) when SQLDynamic is set to true

Question / Problem: 

With eFLOW 5 and eFLOW 6 it is possible to store Dynamic, Setup and System files into the database, rather then keeping them on a harddrive. This functionality is called SQLDynamicStorage. The functionality can be set for every eFLOW System only once after installation. The SQLDynamicStorage setting can be viewed and changed in the following file:

$:\INETPUB\WWWROOT\Tis Web Site\eFlow_5\Bin\ConfigSources\TISConfiguration.config

$:\INETPUB\WWWROOT\Tis Web Site\TisDefaultSTS\Bin\ConfigSources\TISConfiguration.config

The value is:

<UseSqlDynamicStorage>false</UseSqlDynamicStorage>

OR

<UseSqlDynamicStorage>true</UseSqlDynamicStorage>

and must be the same in both file location. 

If you need to check what information is set into your System Configuration Files like DomainSecurity.xml, or others, you can use different approaches.

The first approach is to use the HEX Code in the E_Setup Table of the eFLOW_Management Database. Copy the Stream Binary information (Hex Code) to a Hex Decoder website (like https://www.convertstring.com/de/EncodeDecode/HexDecode) and shorten the Hex Code by the first two character (0x). After Decoding you will see the contents of the file, if it is a text file. 

The second possibility is to use the SQL Server Management Studio to export the file from the table to a harddrive location. See below how to achieve this. 

Answer / Solution: 

Open the SQL Server Management Studio and create a new Query Window. Enter the following script to the window:

###########################################################################################################################

USE [eFlow_Management]

-- Change Path, if necessary
DECLARE @outPutPath varchar(50) = 'C:\TEMP'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max) 
, @folderPath  varchar(max)
  
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )

--Change File Name, if necessary
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [Length],[Name],[Stream] FROM  [dbo].[E_Setup] WHERE [Name] = 'DomainSecurity.xml'
 
SELECT @i = COUNT(1) FROM @Doctable
WHILE @i >= 1

BEGIN
    SELECT

     @data = [Doc_Content],

     @fPath = @outPutPath + '\' +[FileName],

     @folderPath = @outPutPath 

    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
###########################################################################################################################

Change if necessary the Output Folder Path and the File Name of the E_Setup Table file and Execute the script.

If you get error messages about the "Ole Automation Procedures", your database option to use OLE Automatiion procedures have not been activated yet. To do so, consult first your DBA and make sure you are allowed to activate this.

To activate the "Ole Automation Procedures" please use the following Transact SQL statements:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Applies to:  

Product	Version
eFLOW	5.0
eFLOW	6.0