The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

How to Run a SQL Server Job Using Power Apps and Flow

Introduction

The introduction of Power Platform tools addresses a common issue for BI developers: giving more interactivity to end-users. Now, using Power Apps it is possible to write-back values directly to a SQL Server table, simulate what-if scenarios, and so on. Your users can finally “press a button” and make something happen.

In this article I want to show you how to use Power Apps and Flow for running a SQL Server job, something that I always missed. In such a case, you can allow your users to update a small amount of data on-demand.

Layout

In the image below is the layout for the application with the logical inbound and outbound dataflow:

PowerApps   

Input: The user runs an app via any device. Power Apps calls a Flow that calls a SQL Server stored procedure, which calls and executes the job.

Outcome: The job completes either successfully or unsuccessfully. The stored procedure writes the output in a table. Flow reads the table and sends the value to Power Apps that displays the final output to the end user.   

SQL Server

Let’s start with the settings for SQL Server. First of all, we need a job to run. For this demo I will run a job that is doing nothing else but executing a SELECT script. You can change it according to your environment and your needs.

Create a new stored procedure called RunTestSelect for executing the sql script.ipt D

USE [xxxxxx]
GO
/****** Object:  StoredProcedure [dbo].[RunTestSelect]    Script Date: 9/20/2019 2:21:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[RunTestSelect]
AS
SELECT 'test select' as Test_run;
GO

Create a new job RunTestPowerApps. The job is only made by one step. I called it “Select_OK”.

The step executes the stored procedure RunTestSelect

Now we have a job that does something. To run this job, we need another stored procedure.

This second one is to be invoked by Microsoft Flow, which in turn, is called by the Power Apps app.

Create a new stored procedure RunFlowJob

USE [xxxxxxxxx]
GO

/****** Object:  StoredProcedure [dbo].[RunFlowJob]    Script Date: 9/20/2019 6:41:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[RunFlowJob] @job_from_flow nvarchar(50)
AS
BEGIN 

	EXEC msdb.dbo.sp_start_job @job_name = @job_from_flow;

--Check if the job is running
Declare @loop_var int = 1;
while(@loop_var <> 0)
BEGIN
	IF EXISTS(SELECT j.job_id 
			  FROM msdb.dbo.sysjobs J 
			  JOIN msdb.dbo.sysjobactivity A 
				  ON A.job_id=J.job_id 
			  WHERE J.name= @job_from_flow 
			  AND A.run_requested_date IS NOT NULL 
			  AND A.stop_execution_date IS NULL
			 )
		BEGIN
			--PRINT 'The job is running!'
			waitfor delay '00:00:05'
		END 
	ELSE
		BEGIN
			--PRINT 'The job is not running.'
			SET @loop_var = 0
		END 
END


--When the job is finished, test the status
DROP TABLE IF EXISTS dbo.TMP_RunFlowJob_Status;
CREATE TABLE dbo.TMP_RunFlowJob_Status
(Row_Id int NOT NULL IDENTITY(1,1) PRIMARY KEY
,Job_id nvarchar(100) NOT NULL
,Session_id int NOT NULL
,Job_Name NVARCHAR(50) NOT NULL
,start_execution_date datetime NOT NULL
,Stop_execution_date datetime NOT NULL
,Job_Status NVARCHAR(50) NOT NULL
,Elapsed_time int not NULL);

CREATE TABLE #list_running_SQL_jobs
(
    job_id UNIQUEIDENTIFIER NOT NULL
  , last_run_date INT NOT NULL
  , last_run_time INT NOT NULL
  , next_run_date INT NOT NULL
  , next_run_time INT NOT NULL
  , next_run_schedule_id INT NOT NULL
  , requested_to_run INT NOT NULL
  , request_source INT NOT NULL
  , request_source_id sysname NULL
  , running INT NOT NULL
  , current_step INT NOT NULL
  , current_retry_attempt INT NOT NULL
  , job_state INT NOT NULL
);

DECLARE @sqluser NVARCHAR(128)
      , @is_sysadmin INT;

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);

DECLARE read_sysjobs_for_running CURSOR FOR
    SELECT DISTINCT SUSER_SNAME(owner_sid) FROM msdb.dbo.sysjobs;
OPEN read_sysjobs_for_running;
FETCH NEXT FROM read_sysjobs_for_running
INTO @sqluser;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #list_running_SQL_jobs
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @sqluser;
    FETCH NEXT FROM read_sysjobs_for_running
    INTO @sqluser;
END;

CLOSE read_sysjobs_for_running;
DEALLOCATE read_sysjobs_for_running;

DECLARE @ret_code nvarchar(50)

IF ((SELECT  CASE     WHEN a.start_execution_date IS NOT NULL
                       AND a.stop_execution_date IS NULL
                          THEN 'Executing'
                      WHEN h.run_status = 0
                          THEN 'FAILED'
                      WHEN h.run_status = 2
                          THEN 'Retry'
                      WHEN h.run_status = 3
                          THEN 'Canceled'
                      WHEN h.run_status = 4
                          THEN 'InProg'
                      WHEN h.run_status = 1
                          THEN 'Success'
                      ELSE 'Idle'
             END as JStatus

	FROM msdb.dbo.sysjobs j
	LEFT OUTER JOIN (SELECT DISTINCT * FROM #list_running_SQL_jobs) r
		ON j.job_id = r.job_id
	LEFT OUTER JOIN msdb.dbo.sysjobactivity a
		ON j.job_id = a.job_id
			AND a.start_execution_date IS NOT NULL
			--AND a.stop_execution_date IS NULL
			AND NOT EXISTS
			(
				SELECT *
				FROM msdb.dbo.sysjobactivity at
				WHERE at.job_id = a.job_id
					AND at.start_execution_date > a.start_execution_date
			)
		LEFT OUTER JOIN sys.dm_exec_sessions p
			ON p.program_name LIKE 'SQLAgent%0x%'
				AND j.job_id = SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 7, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 5, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 3, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 1, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 11, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 9, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 15, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 13, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 17, 4) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 21, 12)
		LEFT OUTER JOIN msdb.dbo.sysjobhistory h
			ON j.job_id = h.job_id
				AND h.instance_id = a.job_history_id
		LEFT OUTER JOIN msdb.dbo.sysjobsteps t
			ON t.job_id = j.job_id
				AND t.step_id = r.current_step
	where j.name = @job_from_flow) = 'FAILED')
	--return 1
	SET @ret_code = 'KO'
ELSE
	--return 0
	SET @ret_code = 'OK'
;

INSERT INTO dbo.TMP_RunFlowJob_Status (Job_id, Session_id, Job_Name, start_execution_date, Stop_execution_date, Job_Status, Elapsed_time)
select Jid.job_id Job_id, Jid.session_id Session_id, @job_from_flow as Job_Name, ja.start_execution_date start_execution_date, ja.stop_execution_date Stop_execution_date, @ret_code Job_status, datediff(second, ja.start_execution_date, stop_execution_date)
from 
(
	SELECT j.job_id, max(a.session_id) session_id
				  FROM msdb.dbo.sysjobs J 
				  JOIN msdb.dbo.sysjobactivity A 
					  ON A.job_id=J.job_id 
				  WHERE J.name = @job_from_flow
				  AND A.run_requested_date IS NOT NULL 
				  AND A.stop_execution_date IS not NULL
	group by j.job_id
) as Jid

inner join msdb.dbo.sysjobactivity JA
on jid.job_id=JA.job_id
and Jid.session_id = JA.session_id

;

DROP TABLE IF EXISTS #list_running_SQL_jobs;

END;
GO

The stored procedure RunFlowJob takes the job name as input and executes it through the system sp msdb.dbo.sp_start_job. A loop then waits until the job is complete. Then, it reads the status of the job: successful or unsuccessful. The status is fetched and written into a table using a cursor (ok, please don’t blame me, you are free to improve it …).

The final step is to insert the job output and some other info gathered from msdb system database to a table called dbo.TMP_RunFlowJob_Status. This table is subsequently queried by Microsoft Flow to retrieve the job’s status and give feedback to the user.

Just to give you an idea, below is the row stored in my table if I query it:

The column “Job_Status” stores the result from the execution of the job. This is the value returned to Flow.

Microsoft Flow

To let my flow run and access the on-premises database, I first need a gateway. It is exactly the same gateway as Power BI uses, so if you have a gateway already installed, you can use it. Otherwise, you have to install it on any server in your local network. Once the gateway is up and running, go to flow.microsoft.com and log in. Expand the menu Data on the left pane and click on Gateways

Flow should recognize and show all your gateways by default

Next step is setting up a new connection to the on-premises database using the gateway. On the left pane expand Data > Connections

Click New Connection on the upper grey area. Select SQL Server as data source and the type of authentication based on your architecture (in my case is Windows Authentication).

Fill in the dialog window with all the requested data, choose your gateway and click Create.

Now you have a working connection and you can start creating a flow.

Click Create in the left pane. In the editing page you’re presented with three different options for creating a flow. Select the central one: Instant flow

In the dialog window give a name to your flow and select PowerApps as triggering action. Click Create. The first step for your flow is ready. Select + New step

In the Choose an action text bar, type SQL to look for SQL Server. Select it.

The action to be triggered is Execute stored procedure (V2)

Fill in the dialog window with the requested data. The stored procedure name is the one we have created in the previous section for SQL Server: dbo.RunFlowJob

Remember that the stored procedure takes the job’s name as an input parameter. Therefore, a new row appears below asking for the job’s name. This parameter has to be passed by Power Apps; select “Ask in PowerApps” in the related dynamic content dialog window.

Final settings for this step should look like below:

Remember to save your flow before going on.

Add a new step. The action is again related to SQL Server. In this case, select Get row (V2). This action reads one single row from the table where we are storing the output for the job’s execution.

Type in the Server and Database name. The table name is TMP_RunFlowJob_Status. Row id = 1.

The final step for this flow is taking back the value to PowerApps. Add a new step and type PowerApps in the search box. The action is “Respond to a PowerApp or flow”

When you’re prompted, select Text as type of output. Give a custom name to the textbox (I called mine RetCode). Select the field Job_Status from the dynamic content list. Save.

The flow is now complete. The final layout should look like below:

If you want, you can test it. Click on the Back arrow and you’re redirected to the flow main page. Select Run on the upper bar; a window pops up on the right. Type in the name of the job you’ve created in SQL server and select Run flow. Then check the run for your flow.

Power Apps

Now that all the background settings are set up, it’s time to build an app in PowerApps as user interface for running the job and getting back the result.

Log in to PowerApps and create a new canvas app from blank. I’m using the phone layout. Give the app a name and select Create.

You’re redirected to PowerApps online studio, the environment for building and testing the app. Initially what we get is a blank canvas app. In this app, we essentially need two elements: a textbox for typing in the job’s name and a button for triggering the flow we built before.

From the upper menu base select Insert > Text > Text input.

A new text input box is placed on the canvas. Actually, this item has no related action. It is just for user input, but I prefer to change the item name from TextInput1 to a more meaningful name.

Double click on the object in the left pane under the Screen1 menu and change the name for instance in JobName.

Then add a button: Insert > Button. Change the name to Run Button and the displayed text to “Run Job”. The button’s properties are available on the right pane

Then, the most important step: set up the button for executing the flow and running the job in SQL Server Agent.

Select the button on the canvas; from the menu bar go to Action > Flows. In this way, you can associate a flow to a button in PowerApps. A data window displays. Select your flow.

PowerApps links the flow to the button for the OnSelect action. But, the button doesn’t know what to do with this flow, yet. You must complete the formula in order to make it run.

In the formula bar, type the following text: YourFlowName.Run(YourTextBoxName.Text)

Below is how my formula looks like:

In case of OnSelect action for the button, your custom flow is triggered, taking the name of the job you wrote in the text box as input.

Actually, this app already works. You can test it and check what happens. In PowerApps online studio press F5 for switching to demo environment.

Type in your job name and press the button.

Something seems to run, but for time being we don’t know exactly what’s happened. A simple way to check it is to view the job’s history from SQL Server Agent. From the history, you should get confirmation that the job has started when you pressed the button in PowerApps. Amazing!

Just one more piece to make this application suitable for real case: give feedback to the user. As you remember, the flow returns a status to PowerApps. We simply have to intercept this status and display a message to the user, based on the outcome.

A very simple way to do it is to use two native PowerApps functions: Notify and UpdateContext.

Notify displays a banner message to the user at the top of the screen. Notify has two types of arguments we can take advantage of: NotificationType.Error and NotificationType.Success.

UpdateContext creates a variable scoped to the screen. Used in conjunction with Notify, we can use it for displaying outcomes to the app’s user.

Change the OnSelect action for the button, typing in this formula:

If(Result.retcode = "KO", Notify("Job Failed", Error), Notify("Job Executed Successfully", Success))

Press F5 to test the app again. This time, you get notified about the execution for the job, by the context bar on the upper side of the screen

Success

Failure

Conclusion

In this article, I showed you how by using PowerApps and Microsoft Flow you could allow your user to run a SQL Server Agent Job. The application is quite simple, some refinements could be made. For example creating a dropdown list for all the available jobs, giving back some additional info about the job, etc. But, it is beyond the scope for this article.

I wanted to show to you how the Power Platform tools could help your daily jobs, allowing some operations that wouldn’t have been available so far, without writing any code.

Andrea Martorana Tusa
About the author

Andrea Martorana Tusa is a Business Intelligence Team Manager at Würth Phoenix, the IT and consulting company of the Würth-Group. An MVP in the Data Platform category, he has over 20 years of experience working with data and is focused on the entire BI stack: database development, data warehousing, data analysis, reporting, etc. 

Andrea is a frequent speaker at many events such as SQLSaturdays, conferences in Europe and PASS Summit, including PASS Virtual Groups. He is also an author for sqlshack.com, sqlservercentral.com, and UGISS (User Group Italiano SQL Server).

Please login or register to post comments.

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids