SQL output in Email

[mchigurupati]

We have request from a team to execute a SQL query and send the result of output in an email. I was able to run script using Execute Oracle SQL script activity. Then i can send contents using Automation summary. But  user needs contents of Automation summary in email.How can i extract SQL output and send it in an email.

Thanks in advance

--Murali

[Shaun Roberts]

Murali,

 I assume you are using CPO/TEO since you are talking automation summary. The results of a database activity will be stored in an exposed property of that activity called "Result Table".

I would create a local string variable(multi-line). You then would do a For-Each loop through that Result Table and in the loop use the string variable and concatenate each row of the table to that multi-line string. This would convert the table output to a string based variable.

You can then use the Email activity and put the string based version of the table output in the email body. If you want to just spit the data out, then above should work, although you could add the column names and even HTML tags to make it "prettier". Depends on the use-case.

You cannot import the Automation summary into an email outside of just pasting the link to the automation summary. If you want to give more access to the automation summary directory and allow anyone to see them, then I would suggest you setup the automation summary directory as a virtual IIS directory and then include the automation summary link in your Email activity.

--Shaun

[mchigurupati]

Shaun,

yes we are using CPO/TEO. I will try the solution you have suggested.

Thanks

Murali

[mchigurupati]

Shaun,

I have tried the process you have suggested. I have created multiline variable.Added logic For each block then used set variable activity to update multiline variable that i created. Concatenated each item from result table. This is only giving me result of last row as string variable from results table.

Logic i am missing is how to concatenate each row from results table. I am fairly new to Tidal. Any help is appreciated.

Thanks

Murali

[Shaun Roberts · Accepted solution]

In code you would normally do something like

#example python

list_of_people = ['Sam', 'John', 'Bill']

for name in list_of_people:

  all_names+=name

print all_names

#would print SamJohnBill

this would concatenate the strings in python. In CPO you have to use the set variable activity and you have to set the resultant string variable to itself plus whatever you want to add.

So if it's a multi-line string, you update the resultant string(either output or local) and the new value should be

<Current_Resultant_String>

<Whatever you want to add to it>

See the screenshot below and let me know if that makes sense or not.

--shaun

[mchigurupati]

Shaun

Thanks a lot. it worked. We have also added HTML Tags to the output.

Thanks

Murali