Run a Stored Procedure and Send Emails

[apj2]

I have a flow which runs a SQL server stored procedure (on premise gateway) and returns the expected rows in the OUTPUTS Body section of the 'Execute stored procedure (V2)' action.

What I am looking for are detailed examples which show how to write emails in following scenarios –

1. A flow which runs a SQL server stored procedure and writes a customized email for each row returned
2. A flow which runs a SQL server stored procedure and write the entire result in one email body. The result should be in the form of an html table.

Does anyone have this documented in complete examples (all steps clearly explained)?   

[abm]

Hi,

The below example demonstrates how to retrieve data from SQL via SQL query. So in your case you need to select action SQL Server - Execute Stored Procedure and select the stored procedure name. Also this demonstrates how to get all results in one email.

https://blog.sandro-pereira.com/2018/09/23/microsoft-flow-how-to-pass-the-sql-data-table-results-in-... 

If you want to send email for each row then do not do the append operation. Add the send email action inside the loop so that you can map each row values in your email action.

Thanks

[apj2]

Hi abm,

Thank you for replying to my question.

Right from the start I had problems when trying to follow your blog.

I was not able to perform the action 'Execute an SQL Query (V2)' thru the on-prem gateway.

-------------------------------------------------------------------------------------------------------------------

Therefore I decided to stick with my stored procedure method, append the result set into an array, build an HTML table and finally email this out.    Unfortunately I am still having problems with the syntax at key steps.

Following is the flow

I am having problems with addressing the array   ID   field in the 'Create HTML Table' action.    What is the correct syntax to use for -   Expression:    lines()?['Table1']?[0]?['ID']   is not working.      I have tried many variations on this however none worked correctly.

I believe the 'Append to array variable' is working correctly with the Value set to ResultSets.

Any help would be greatly appreciated.

[abm]

Hi @apj2 

Could you try removing the append to array variable step and directly map the result sets under Create HTML table? Then map the ID under the custom header ID.

Thanks

[apj2]

Hi abm,

Again thank you for trying to help me.

When I follow your suggestion this happens --

I have no access to any objects from the stored procedure.   I believe Create HTML table wants an array as an input.

-----------------------------------------------

Next try - put a Compose component between the stored proc and the Create HTML table step.  

This at least gives access to the stored proc ResultSets or Body objects.  However what is the syntax to address just one column of the Outputs object?

--------------------------------------------

Try 3 - Run stored procedure, Parse JSON out of the ResultSets object, and use the output to feed the Create HTML table step.    That doesn't work because the Create HTML table step errors out with - 

BadRequest. The 'from' property value in the 'table' action inputs is of type 'Null'.  The value must be of type 'Array'.     

Eventhough in the Parse JSON step the Schema shows Table1 to be of type array.     I Don't understand.

Overall it's an extremely frustrating experience trying to get flow to do something 'Non-Template'.    And finding solid Microsoft documentation on flow Actions is near impossible (at least it seems that way).

Again thank you for trying to help me with my problem.

[abm]

Hi @apj2 

Thanks for your reply.

I will have a look at this and get back to you.

Thanks

[abm]

Hi @apj2 

I don't have a SQL on prem connectivity now. So this is what I did.

First declared a compose step and use some sample data similar like yours.

Next I used a Parse JSON action

Below is the schema I used.

{

"type": "object",

"properties": {

"Table1": {

"type": "array",

"items": {

"type": "object",

"properties": {

"ID": {

"type": "integer"

},

"SLARegion": {

"type": "string"

},

"MeasuredKPI": {

"type": "string"

}

},

"required": [

"ID",

"SLARegion",

"MeasuredKPI"

]

}

}

}

}

Finally I used Create HTML table action and map some of the elements from the above step.

Here is my test results:

[apj2]

Hi abm,

Thank you very much for your help on this.   

I had tried below sequence earlier however my JSON schema in the 'Parse JSON' step was incorrect as I had the OutputParameters & ResultSets objects (coming from the Execute Stored Procedure step) still included.   Once I removed these objects from the schema the flow properly passed the Table1 array to the 'Create HTML Table' step and the flow ran through fine.

Again I greatly appreciate your help.

[abm]

Hi @apj2 

Thanks for your reply.

Let me try it from my side and get back to you. 

[abm]

Hi @apj2 

Please follow these steps

First I declared a variable and pass the value.

Next I use the Parse JSON action

This is the above schema

{

"type": "object",

"properties": {

"OutputParameters": {

"type": "object",

"properties": {}

},

"ResultSets": {

"type": "object",

"properties": {

"Table1": {

"type": "array",

"items": {

"type": "object",

"properties": {

"Id": {

"type": "integer"

},

"SLARegion": {

"type": "string"

},

"MeasuredKPI": {

"type": "string"

}

},

"required": [

"Id",

"SLARegion",

"MeasuredKPI"

]

}

}

}

}

}

}

Finally create HTML Table

Here is my test results:

If you need any further help please let me know.

[Satwik]

Hi @apj2 ,

Try following below steps:

Step 1: For ParseJSON Use Sproc output "ResultSet" as Content and Paste ResultSet JSON code (You can find it by doing a test run of Execute stored procedure (V2) Step) in "Generate from sample" which automatically creates its schema.

Step 2: For Create HTML Table use Table1 (one of the output value from ParseJSON) as a value to "From" and Automate for Columns. 

These above steps will fix your issue, below SS for reference.

Thanks.

[woyzcyechowski]

This solved my problem using "execute a SQL query" and storing the results in an HTML table. It's also way faster than any other method that I have come across since it does not require an "apply to each" loop. Big thank you!!!

[nejhade]

This Worked for me to Thanks a tons.