Data Driven Subscription - SQL Server Standard Edition

/*
The unit of work implemented by Boomerang is called Event. 
The Event is represented by a single record in the EVENT_MASTER table. 
Before creating the event key (uniqueidentifier), that defines and 
hold the data driven subscription together, it is need to be declared 
and set.
*/
Declare @gKey uniqueidentifier; set @gKey = newid(); -- Key for my entire event
Declare @jKey_file uniqueidentifier; set @jKey_file = newid(); -- Key for my file out job
Declare @aKey_file uniqueidentifier; set @aKey_file = newid(); -- Key for my file out report paramaters
Declare @jKey_email uniqueidentifier; set @jKey_email = newid(); -- Key for my email notification job

Insert EVENT_MASTER (gKey, Source, Created_By, Str1)
Values (@gKey, 1, 'domain\username', 'My data driven subscription');

/*
Each event consists of one or more jobs. A job represents an actual 
unit of delivery, such as email, fax, or print. Each job declares its 
own content as well as how and where the job’s output will be delivered. 
For example, a print job must define the path to a printer. Several 
jobs can make up a single event. The jobs can be entered in any order 
but there is no means to control which job will execute first or last.
*/
Insert OUT_FILE (gKey, jKey, Path, Mode)
Values (@gKey, @jKey_file, '\\unc_path_where_the_report_will_be_saved\', 1); 

Insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format], Name)
Values (@gKey, @jKey_file, @aKey_file, 2, 'Misc/Sales_by_Region', 'XLS', 'File_Name') 

/*
Next step is to specify what content of the OUT_FILE job. 
This is done in EVENT_CONTENT and can look like this:
*/
Insert CONTENT_PARAMETER (aKey,[Name],[Value]) 
Values (@aKey_file,'Sales_Period', convert(varchar(2), datepart(month, getdate())))

/*
This e-mail will notify a user that the OUT_FILE job stored 
a report ('Misc/Sales_by_Region') on a file server 
('\\unc_path_where_the_report_will_be_saved\')
*/
Insert OUT_EMAIL (gKey, jKey, IncludeKey, [ReplyTo], [From], Subject, Body)
Values(@gKey, @jKey_email, 0, 'Datasubsriptions@my_domain', '"e-mail display name" <'">Datasubsriptions@my_domain>', 'Sales by Region', 'A copy of the monthly budget report have now been generated and can be found on the file server in this location: \\ssss\ss');
Insert OUT_EMAIL_RECEPIENT (jKey, Type, Email)
values (@jKey_email, 2, 'recepient@domain.com')

/* To give the OUT_FILE job time to processes the email 
notification can easily be delayed by setting the 
EVENT_STATUS.Run_When field:
*/
Update EVENT_STATUS
set Run_When = dateadd(hour, 1, getdate()) 
where jKey=@jKey_email


/*
Last step is to release the event to be processed. 
In this case Boomerang will send an email and store a 
SQL reporting server report to a directory i.e. the two 
jobs OUT_EMAIL and OUT_FILE specified above. To release the 
event simply set the status to 0 as shown below:
*/
Update EVENT_MASTER set Status=0 where gKey=@gKey;