Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a few Sql Scripts that I would like to run every day on my Sql Server(s). How can I query multiple times and have the output similar to the following:

Health check for <server>


Query Title 1:

<query results 1>


Query Title 2:

<query results 2>

I am checking things like last good back up, uptime etc so they are not related to each other. I am planning to use sp_send_db_mail inside of a Sql Job. Is this the best way of accomplishing this task?

Ideally, I would only like one email.

Update

After batteling for ages!!! The solution I have found so far is to put it into a sproc and then execute the sproc like so:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'<email>',
@body= @body, 
@subject = @subjectt,
@profile_name = @ProfileName,
@query ='exec test..sp_checks'

Is this ok for multiple select statements?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

This is more just a note on the philosophy rather than the technique.

If you did this, I would make sure to set some limits on it so that it only sent out emails when you were outside those limits.

For example, if you're CPU is spiking to 100% frequently, I would send out the email. However, if there's no problems with the CPU, I would explicitly, intentionally not send the email.

If you get a daily email from your database, you're going to start ignoring them after a while. But if you only get them when there's something that you think you should check for, you're more likely to look at the emails.

Also, I'd make those limits configurable (and possibly even make flags so that specific queries can be turned on or off).


Regarding your question of multiple selects inside a single email, it sounds like there are two options:

  1. Get the results for each query and format them into a string that contains everything you want.

  2. Merge all the different results into a single query and then set your @query to that. (Alternatively, sticking all the results into a temporary table and querying the temp table.)

It is possible, I should note, to stick a query in the @query as well as sticking the results of a second query in the @body (per here and here), but that only allows you two statements.

I would try option one inside of a stored procedure. The trick would be formatting the table into the string. However, it's the most likely to work correctly.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.