Archive | Uncategorized RSS feed for this section

Sending an Outlook Calendar meeting event via sp_send_dbmail

1 Oct

I came across a request from the business department that needed an Outlook Calendar meeting event (a Calendar reminder) whenever a specific type of data was recorded in the database.

I’ve created a procedure. In it I performed the check required by the business. In case rows returned, I sendtthe information in the mail with an ICS file attachment that contains details on the necessary reminder.

All the user has to do was to open the ICS attachment, and to click Save&Close to add it to his calender.

Prerequisites : A functioning Database mail configuration in your database.

The main part of the procedure is the sp_send_dbmail proc that sends the mail to the user

 EXEC msdb.dbo.sp_send_dbmail
     @recipients ='DatabaseLady@HelloWorld.com'
    ,@profile_name = 'ProfileName'
    ,@subject = 'A Friendly reminder'
    , @query = '
--
SET NOCOUNT ON;
--
SELECT ''BEGIN:VCALENDAR''
+ CHAR(13)
+ ''PRODID:-//My Company//Company Calendar//EN''
+ CHAR(13)
+ ''VERSION:2.0''
+ CHAR(13)
+ ''METHOD:PUBLISH''
+ CHAR(13)
+ ''BEGIN:VEVENT''
+ CHAR(13)
+ ''CLASS:PUBLIC''
+ CHAR(13)
+ ''DESCRIPTION:Please make a reminder''
+ CHAR(13)
+ ''SUMMARY:Please make a reminder''
+ CHAR(13)
+ ''DTEND:20140930T190000''
+ CHAR(13)
+ ''DTSTART:20140930T190000''
+ CHAR(13)
+ ''LOCATION:''
+ CHAR(13)
+ ''END:VEVENT''
+ CHAR(13)
+ ''END:VCALENDAR''
'
 , @attach_query_result_as_file = 1
 , @query_result_header = 0
 , @query_result_separator = 'CHAR(10)+CHAR(13)'
 , @exclude_query_output = 1
 , @query_attachment_filename = 'DividendEvent.ics'

The interesting part is the @query parameter.
In this part, you create an iCalendar file that Outlook support and recognize.

BEGIN:VCALENDAR
PRODID:-//My Company//Company Calendar//EN
VERSION:2.0
METHOD:PUBLISH                                          Post notification of an event.
BEGIN:VEVENT
CLASS:PUBLIC
DESCRIPTION:Please make a reminder       The event description.
SUMMARY:Please make a reminder              The event title.
DTEND:20140930T190000                             Event end time, in ISO 8601 format
DTSTART:20140930T190000                    Event start time, in ISO 8601 format
LOCATION:                                                          The venue name.
END:VEVENT
END:VCALENDAR

More information on iCalendar format:

More information on sp_end_dbmail:
http://msdn.microsoft.com/en-us/library/ms190307.aspx

Advertisements

Hello world!

30 Nov

Welcome to WordPress.com! This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!