How to Run a BAT File in Strategy

Occasionally, you may need to run a BAT file directly from Strategy (former MicroStrategy) to automate tasks or integrate workflows. While this is a viable option, executing it from an SQL Server comes with potential security risks. This guide provides a step-by-step approach to setting up your environment, configuring permissions, and safely executing a BAT file through SQL Server while mitigating security concerns.

Step 1: Preparing the Environment

Before executing a BAT file, ensure that your environment is configured correctly.

1.1 Install Java (If Required)

If your BAT file was created in Qlik Talend, you may need to install OpenJDK.

1. Download OpenJDK from a trusted source.
2. Extract it to a desired location (e.g.,

   C:\Program Files\openlogic-openjdk

   ).
3. Set the system variable:
   • Variable Name:

     JAVA_HOME

   • Variable Value: C:\Program Files\openlogic-openjdk
4. Add the Java bin directory to the system’s environment variables.
5. Verify the installation by running:

   java -version

   .
   If you can see the version details, Java is correctly set up.

Step 2: Copying the BAT File to SQL Server

Ensure that your BAT file is accessible by the SQL Server.

1. Locate the BAT file on your local machine.
2. Copy it to the SQL Server directory, where it will be executed.
   • Example directory:

     C:\ETL_job\Get_Data_2023_0.1\Get_Data_2023\Get_Data_2023_run.bat

3. Verify that the file is correctly placed and accessible.

Step 3: Setting Folder Permissions

If the BAT file modifies, creates, or deletes files, appropriate permissions must be granted.

1. Identify the SQL Server service account that runs the SQL Server.
2. Grant Read & Execute and Modify permissions to the BAT file’s folder.
3. Ensure that subfolders inherit the correct permissions.

Step 4: Create a SQL Procedure

Now, create a stored procedure in SQL Server.

CREATE PROCEDURE kl_run_steam_update
AS
BEGIN
    -- Enable xp_cmdshell
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;

    -- Execute the BAT file
    EXEC xp_cmdshell 'C:\ETL_job\Get_Data_2023_0.1\Get_Data_2023\Get_Data_2023_run.bat';

    -- Disable xp_cmdshell
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
END

After executing the script, you can run the procedure using:

exec [dbo].kl_run_steam_update

If you encounter an error, enable advanced options first:

sp_configure 'show advanced options', 1;
RECONFIGURE;

Step 5: Executing the Procedure in Strategy

To run the BAT file from Strategy, you can use Freeform SQL:

1. Open a Freeform SQL Report.
2. Write the command to execute the stored procedure.
3. Add VLDB option to run the procedure directly from SQL Server.
4. Run the report to execute the BAT file.

Once completed, results will be displayed, or a timeout may occur if the execution takes too long.

Conclusion

This guide walked through executing a BAT file from Strategy step by step. If you have any questions, our team of experts is here to help. We specialize in Strategy solutions and can assist you with integration, automation, and optimization.

Contact us for more details on how we can support your business with Strategy solutions!