Sql job to execute multiple queries and email

Question

Sql job to execute multiple queries and email

happy55

Ten Centuries

Points: 1243
More actions
July 14, 2014 at 2:08 pm

#290896

I have to create a SQL job which will run around 50 queries and email results when the query gets some results.
These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .
What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

it depends on the shape of the data you want to report;

Can All 50 queries results be placed in a temp table, or do you need each resultset? just returning information about each resultset?

I know I have a CLR that i can pass a Query, and it uses the current connection to return an html-formatted resultset as a varchar(max); so i could append 50 results to a varchar(max) and use that as the body of an email;(you could do the same all in TSQL with FOR XML of course)

what if the query returned a millionbillionrow table, though....

kind of intriguing question, i'd like to hear some more details.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

happy55 Ten Centuries Points: 1243 More actions · Answer 2

Thanks for the reply.

I will need each result set in case the query returns any results.

lets say out of 50 queries 5 give some results so i will need 5 result set and send out 5 emails

with different subjects and each email having one file attached.

they are system error so there will never be million rows or may be I can do a rowcount before executing the query

gordon.feeney SSC Enthusiast Points: 186 More actions · Answer 3

SSChampion's response is a valid one. How big can any or all of your results become? If one or more queries return hundreds, or even thousands of rows, well I probably wouldn't want to get an e-mail like that.

You mentioned SSIS in your original question. Taking the BI element a step further have you considered maybe an SSRS report which you send to your distribution list either as an attached report or containing a link to the report.

Gordon.