Another solution that might be more efficient if you run many reports at night is to create a table to store the various statuses of a night's reports. When all the reports should have finished, a stored procedure could execute and send an email message about that night's activities. To implement this solution, create a table like the following:
CREATE TABLE report_status
(
report_name varchar(100) NOT NULL,
start_time datetime NOT NULL,
finish_time datetime NULL
)
GO
The reports that execute insert their names and start times at the beginning of the report. When a report finishes, it updates the existing record with its finish time. Because the report also records the finish time, the existence of NULL values would signify that certain reports took an inordinate amount of time to run. Report Status could be another stored procedure. You could then set up a stored procedure to select all the table's records and send an email message containing the status of the previous night's reports to the responsible party, as the following example shows:
CREATE PROCEDURE sp_send_report_status
AS
Master..xp_sendmail @recipients =
"responsible@get-big.com",
@query = "SELECT * FROM report_status",
@subject = "Status Report"
GO
You could add a line to the sp_send_report_status stored procedure to send an email message to the DBA if the stored procedure found a NULL value in the finish_time field; this email would notify the DBA that a running report is having problems. To make sure the report_status table didn't grow too large, you would either have to archive the report_status table often or truncate it on occasion.
Neither Rain Nor Sleet
Take advantage of SQL Server's email funtionality to help keep your server running. You can set up SQL Mail to notify the appropriate operator of potential problems, or you can use the xp_sendmail extended stored procedure for the same purpose. With SQL Server 2000, the email function is easier to set up than ever before. By using email to notify an operator of a potential problem, tasks that fail get the attention they need. However, sending both success and failure messages might lessen the impact of messages requiring attention. Remember, neither rain nor sleet nor any other weather situation can keep SQL Mail from its intended rounds.
End of Article
Prev. page
1
2
[3]
next page -->
