Database reporting alerts

Question

I am about to make a reporting/alerting mechanism that will periodically examine state of one of my databases that is constantly updated and send reports/fire alerts if needed depending on the data in the tables.

I am not familiar with MS BI stack. I played a bit with reporting services but as far as I can see it can only send periodic reports. I need some quite moderately simple logic before sending a report that will decide whether the report needs to be sent at all.

Should I go with custom external solution, some core SQL Server stuff (triggers + CLR support?) or there is an existing tool in BI stack that will suit my needs?

Answer 1

Depends on how sophisticated you want your reports to be.

The simple solution is to create SQL stored procedures that generate emails with reports as embedded/attached HTML or as attached csv files.

The stored procdure can be called from SQL Agent using the flexible scheduling and error reporting that is already built in.

Answer 2

Good point by @Jimbo that it depends on how sophisticated you want this to be.

For a start just go with stored procedures and attaching results as email attachment. See this for more details on how to implement it. This is from MSDN

[ @file_attachments= ] 'file_attachments' Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

[ @query= ] 'query' Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

[ @execute_query_database= ] 'execute_query_database' Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.

[ @attach_query_result_as_file= ] attach_query_result_as_file Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.

When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

[ @query_attachment_filename= ] query_attachment_filename Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.