Sunday, October 25, 2009

Windows Server 2008 R2

I found a handy free eBook on Windows Server R2 from Microsoft here link

Thursday, October 22, 2009

Reporting Services - Data -Driven Subscription

 In Reporting Services data driven subscriptions are used to run a query on a scheduled basis which returns a list of recipients to receive a report.  What I have tried to do here is to show a simple example which will use this functionality to monitor an application and email out a report when any alerts that have been triggered.

You need the enterprise version of reporting services to use data driven subscriptions. In addition SQLAgent needs to be running and the data source must use stored credentials.

The example will use a simple table to hold details any events that need to be reported on. The application will insert rows into this table for each event that needs reporting on

create table ReportTriggers(
    TriggerID int IDENTITY(1,1) NOT NULL,
    TriggerDate smalldatetime NULL,
    TriggerDescription varchar(50) NOT NULL

The stored procedure prGetReportTriggers will be queried and it will only populate the email recipient list if new records have been added to the ReportTriggers table that have not already been reported based on the fact that the triggerdate is null

Create procedure prGetReportTriggers
    if (select COUNT(*) from ReportTriggers
        where TriggerDate is null) > 0
                select '' as Email
                update ReportTriggers set TriggerDate = getdate()
                where TriggerDate is null
                select NULL as Email

The report that will be called by the subscription should use the following SQL to get those records that have just been updated by the prGetReportTriggers stored procedure.

select * from ReportTriggers
where TriggerDate = (select max(TriggerDate) from ReportTriggers)

Follow the following basic steps to create the data driven subscription 

Call the stored procedure 

Populate the To value with the returned email list

Set up the subscrption schedule to run as frequently as you require