How to run BAT file in MicroStrategy

24 Jul 2024

Dificulty ★★★☆☆

Prologue

Occasionally, you may want to run a BAT file directly from MicroStrategy.
In this guide, we will discuss how to execute it from an SQL Server.
While this is a viable option, keep in mind that it poses a security risk.

JAVA (optional)

If you created the BAT file in Talend, you need to perform an additional step.

On the SQL Server, download (OpenJDK)[https://www.openlogic.com/openjdk-downloads] , and extract it to the desired location (in this tutorial it was C:\Program Files\openlogic-openjdk).

Add system vatiable
JAVA_JOME
C:\Program Files\openlogic-openjdk

In Enviroment Varables add path to Java bin location

Test Java in the Command Prompt (CMD) by running:
Java –version

If you get a version output, everything was set up correctly.

Now, restart the SQL Server service.

Copy BAT file to SQL server

Copy the bat file to the server
In my case, I copied the entire Talend job to the following location:
C:\ETL_job\Get_Data_2023_0.1

so the BAT file is in location
C:\ETL_job\Get_Data_2023_0.1\Get_Data_2023\Get_Data_2023_run.bat

The files I will be processing are located at:
C:\ETL_STEAM

Setting Folder Permissions on SQL Server

On SQL server chek the name of user that is running ‘SQL server’

Grant the necessary permissions to the folder that will be used to process files.

Set permissions for all subfolders as well.

Create a procedure to run the BAT file

To create a stored procedure that runs the BAT file, use the following SQL code:

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

/*Execute the batch file*/
EXEC xp_cmdshell 'C:\ETL_job\Get_Data_2023_0.1\Get_Data_2023\Get_Data_2023_run.bat';

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

After creating the procedure, you can test it by running:

 
exec [dbo].kl_run_steam_update

if you get an error
activate the addvanced options and try aggain

 
sp_configure 'show advanced options', '1'
RECONFIGURE

Microstrategy

You can use a standard report, but I prefer Freeform SQL,
where I can write custom text when the procedure was executed.

and add VLDB option to run procedure from SQL

Once this setup is complete, the user simply needs to run the report.

After the procedure completes, the user will either see the results or experience a timeout