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?


2 Answers 2


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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