2

I currently have a maintenance plan in place that does a backup, re-indexing, etc. If a task fails, it sends me an email alert. I would like to add a "T-SQL Statement Task" that will execute a query and send me an email alert based on the results of the query.

For example:

Select Cast(case when count(inventory.items) > 1000 then 1 else 0 End as bit) as SendAlert
From Inventory

The maintenance plan's alert function seems to be triggered whether the Query executed successfully or not -- RATHER THAN from the Query Result.

Can I accomplish what I want from within a maintenance plan or is there a better way to do this?

2

I would like to add a "T-SQL Statement Task" that will execute a query and send me an email alert based on the results of the query.

You can do this using a TSQL Task and using sp_send_dbmail as below :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'your mail profile',
    @recipients = 'dba@company.com',
    @body = 'results -- some body goes here',
    @subject = 'Sensible subject goes here ',
-- to send file attachments (if any)
    @file_attachments = 'L:\logs.TXT',
  -- send query results 
    @query = 'Select Cast(case when count(inventory.items) > 1000 then 1 else 0 End as bit) as SendAlert
From Inventory';
1

I guess that you can raiserror when the option is false (or when you want to be alerted). It will write an error message to even log with an error number that you selected. Then it is just create an alert based on that error number How to create an alert using an error number (Enterprise Manager)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy