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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have an instance that randomly spikes the CPU. I want to create an alert that fires on CPU over 90% and automatically calls a job that runs sp_AskBrent and emails me the output. However, the output is unreadable in either Text or HTML output. It doesn't even go well into an Excel spread sheet. How can I get the info in a readable format?

share|improve this question
3  
You can check my answer on another question to run it under PowerShell, it will output an HTML report of the data. Depending on the version of SQL Server you can set that up as a SQL Agent job (PowerShell step), then add in PS code to email the file. – Shawn Melton Jan 5 at 20:00
    
@ShawnMelton yeah, I came here to post that as well, powershell running it is fairly trivial to then do adhoc formatting. – ConstantineK Jan 5 at 23:20

Make the alert run a job that runs the EXEC msdb.dbo.sp_send_dbmail with the @query parameter:

EXEC dbo.sp_send_dbmail @profile_name = 'mail_profile'
    , @recipients = 'some@mail.com'
    , @subject = 'some subject'
    , @body = 'the body text goes here'
    , @query = 'EXEC sp_AskBrent;'
    , @attach_query_result_as_file = 1;

The @attach_query_result_as_file = 1 parameter will send the results as an attachment in text format, which should certainly be readable. If you want, you can export the results in comma-delimited format using the @query_result_separator parameter.

From the MSDN page for sp_send_dbmail:

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order Count, and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

There are many options for that stored proc; check the MSDN page and likely you'll get the output exactly how you want it.

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.