use Boomerang
go
begin
set nocount on
set xact_abort on
declare @gKey uniqueidentifier; set @gKey = newid()
declare @jKey uniqueidentifier;
declare @aKey uniqueidentifier;
-- first off: create new event
insert EVENT_MASTER (gKey, Source, Created_By ) values (@gKey, null, 'denisp')
-- create email job
if(1=1) begin
-- reserve a job key
set @jKey = newid()
-- add a job, uncomment one of the following:
-- email job with subject and priority (being 1 for top important thru 5 for the least, default is 3)
-- no body, default ReplyTo (see server settings)
-- see x-priority documentation at http://tools.ietf.org/html/rfc4356
--insert OUT_EMAIL (gKey,jKey,Subject,XPriority) values (@gKey,@jKey,'test subject',5)
-- email job with body, reply-to & subject line
-- this example also demonstrates options for the tracking key - 0 = include none, 1 = at the top (def.), 2 = bottom
--insert OUT_EMAIL (gKey,jKey,Subject,Body,ReplyTo,IncludeKey) values (@gKey,@jKey,'test','this is a test, ignore','replyto@mydomain.com',0)
-- email job (same as above) with specific From address
insert OUT_EMAIL (gKey,jKey,Subject,Body,ReplyTo,[From],IncludeKey)
values (@gKey,@jKey,'Please reply to this one',null,'replyto@mydomain.com','"Human description" <from@mydomain.com>',0)
-- add recepient(s)
-- add a single e-mail recepient
insert OUT_EMAIL_RECEPIENT (jKey,[Name],Email) values (@jKey,'John Smith','john.smith@theirdomain.com')
-- add a single BCC e-mail recepient; Type - 1 = TO, 2 = CC, 3 = BCC
insert OUT_EMAIL_RECEPIENT (jKey,[Name],Email,Type) values (@jKey,'David Hickory','david.hickory@theirdomain.com',3)
-- add a CC semicolon-separated list
exec sp_Add_Email_List @jKey=@jKey, @list='someone_else@yahoo.com;"Mr. Smith" <mr_smith@yahoo.com>', @type=2
-- add attachement(s)
if(1=1) begin
-- add attachment
-- add attachment as stream of bytes
-- with optional file name (otherwise automatic)
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Stream,[Name]) values (@gKey,@jKey,@aKey,0,convert(image,'test'),'test.txt')
-- add attachment from external file
-- with optional file name (otherwise original)
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Name]) values (@gKey,@jKey,@aKey,1,'c:\example.txt','test.txt')
-- add SQL RS report attachment in MHT format;
-- other format options include: XLS (or EXCEL), PDF, XML, CSV, MHTML, HTM (or HTML, HTML3.2, HTML4.0), JPG (or JPEG), TIF (or TIFF), GIF, PNG, BMP, or IMAGE
-- for more info on formats refer to SQL RS documentation
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Format]) values (@gKey,@jKey,@aKey,2,'Metrics/FI/FI_Scorecard','MHT')
-- add SQL RS report attachment in server default format (see service settings) as an in-line attachement, i.e. shows inside body
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,IsAttachment) values (@gKey,@jKey,@aKey,2,'Metrics/OD/OD_Scorecard',0)
-- add SQL RS report attachment in HTML format as an in-line attachement, i.e. shows inside body
-- Body field is optional, if not specified, then only content of the report will fill the body,
-- otherwise will appear mixed with your body prepending the report
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,IsAttachment,[Format]) values (@gKey,@jKey,@aKey,2,'Metrics/IT/IT_Scorecard',0,'HTML')
-- add SQL RS report attachment in PDF format
-- also pass a single parameter to SQL RS report
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Format]) values (@gKey,@jKey,@aKey,2,'DOCS/PICKLIST_PARTS','PDF')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0006115619')
end
end
-- create print job
if(0=1) begin
-- reserve a job key
set @jKey = newid()
-- add a job with reference to printer
insert OUT_PRINT (gKey,jKey,Printer_Path) values (@gKey,@jKey,'\\server\bar_code_printer010')
-- add SQL RS report content with a single parameter, automatic orientation and 100% scale factor
-- note: only Src_Type = 2 supported, i.e. SQL RS
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'RA/RA_Label')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0008019231')
-- add SQL RS report content with a single parameter, automatic orientation and 100% scale factor
-- note: when report is designed for landscape output, it will be automatically recognized
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/landscape_label')
-- add SQL RS report content with a single parameter,
-- orientation (1 = protrait, 2 = landscape)
-- 50% magnification, (scale can either be any floating-point positive number or zero, which also means 100%; 1 = 100%)
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Scale,Orientation,Path) values (@gKey,@jKey,@aKey,2,1.5,1,'DOCS/RA_Label')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0008022972')
end
-- create MS fax job
if(0=1) begin
-- reserve a job key
set @jKey = newid()
-- add a job with reference to printer
if(0=1)
-- without cover page, reference to destination phone number
-- and specific fax server (otherwise service-default)
-- note: status tracking is only possible with service-default fax server
insert OUT_FAX (gKey,jKey,Fax_Path,Receiver_Fax_Number) values (@gKey,@jKey,'\\server\fax','1-999-123-4567')
else
-- with cover page
-- cover template may include names for templates installed on MS fax server
-- for more info consult MS fax server documentation
insert OUT_FAX (gKey,jKey,Fax_Path,Receiver_Fax_Number,
Receiver_Name,
Sender_Fax_Number,
Sender_Name,
Cover_Template,
Cover_Subject,
Cover_Note
) values (@gKey,@jKey,'\\server\fax','1-999-123-4567',
'Robert Davis',
'1-999-470-1200',
'Denis P',
'generic',
'This is test',
'Please pass on Robert'
)
-- fax content
-- add SQL RS report content with single parameter, 100% scale and automatic orientation (1 = portrait, 2 = landscape)
-- note: only Src_Type = 2 supported, i.e. SQL RS
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/PICKLIST_PARTS')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0006103648')
-- add SQL RS report content with single parameter,
-- scale to stretch and fill entire page
-- (scale can either be any floating-point positive number or zero, which also means stretch/shrink to fill entire page; 1 = 100%)
-- and automatic orientation (1 = portrait, 2 = landscape)
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Scale,Path) values (@gKey,@jKey,@aKey,2,0,'DOCS/RA_Label')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0008019231')
-- add SQL RS report content with automatic orientation and 100% scale factor
-- note: when report is designed for landscape output, it will be automatically recognized
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/landscape_label')
end
-- create file job
if(0=1) begin
-- reserve a job key
set @jKey = newid()
-- file job
-- with reference to output path
-- note: stinky service security account must have write access to specified path
insert OUT_FILE (gKey,jKey,Path) values (@gKey,@jKey,'C:\')
-- file content
-- add attachment as stream of bytes
-- with optional file name (otherwise automatic)
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Stream,[Name]) values (@gKey,@jKey,@aKey,0,convert(image,'test'),'test.txt')
-- add attachment from external file
-- with optional file name (otherwise original)
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Name]) values (@gKey,@jKey,@aKey,1,'c:\example.txt','test.txt')
-- add SQL RS report content with single parameter
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path) values (@gKey,@jKey,@aKey,2,'DOCS/PICKLIST_PARTS')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'so','0006103648')
end
-- (optional) fill job status structure
-- otherwise service-default values will be used, see server settings
if(1=0) begin
update EVENT_STATUS set
Run_When = '2010-12-31 08:00:00.000' -- 8:00AM on December 31 of 2010
Retry_Times = 10, -- retry up to number of times in event of system error
Retry_Period = 600 -- seconds between retries
where jKey=@jKey
end
-- flag event ready
update EVENT_MASTER set Status=0 where gKey=@gKey
end