New open source project: Send calendar invites from within SQL Server

Looking for a way to send calendar event invitations from within SQL Server?

Did you try that one hack where you use sp_send_dbmail to generate an ics attachment file, and then found that it’s still not being identified as a calendar event in the e-mail program?

Then fret no more, my friends, because I’ve been there, and out of sheer desperation of not being able to find a properly working solution, I’ve decided to build one myself. And now it’s available for your wide consumption as an open source project!

Download

First, before I start boring you with the details, here’s a link to the GitHub Releases page:

Still here? Okay then let’s dive in.

Background

One of the main disadvantages of the built in sp_send_dbmail procedure, is that it has no functionality to modify the headers of the messages it’s sending. Incidentally, modifying the message headers is exactly what’s needed when you want your message to properly be identified as a calendar invitation.

Therefore hacks such as these are not good enough.

After some research, I found that the only way to send emails with custom headers from within SQL Server is, well… By not sending them from within SQL Server. Or rather, it can only be done using a CLR method.

I’ve been looking over the net for something existing that would give me this solution, but all I could find are bits and pieces of incomplete or overtly complicated solutions, and a few relevant stack overflow threads.

So, I eventually found myself constructing a complete solution by myself out of all these bits and pieces, almost like building a jigsaw puzzle.

Prerequisites

Besides the obvious which is having access to a working SMTP server and having clr enabled on your SQL Server instance, the only other thing you need is a database with the trustworthy setting on. This is because of the unsafe permission set required by the assembly (which is, naturally, needed for sending anything outside the SQL Server instance).

The necessary UNSAFE permission set means, by the way, that this solution obviously cannot work in Azure SQL Databases.

EDIT: I’ve already created a new release that uses a signed certificate as a safer alternative to the TRUSTWORTHY setting. So, that’s no longer needed.

Installation

If you have SSDT, you can open the SQL server project and publish it to your database of choice. Alternatively, you can use this simple installation script that sets everything up for you in your database of choice.

Syntax

exec sp_send_calendar_event
	[   [ @profile_name = ] 'profile_name' ]
	[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
	[ , [ @copy_recipients = ] 'copy_recipients [ ; ...n ]' ]
	[ , [ @blind_copy_recipients = ] 'blind_copy_recipients [ ; ...n ]' ]
	[ , [ @from_address = ] 'from_address' ]
	[ , [ @reply_to = ] 'reply_to' ]
	[ , [ @subject = ] 'subject' ]
	[ , [ @body = ] 'body' ]
	[ , [ @body_format = ] 'TEXT | HTML' ]
	[ , [ @importance = ] 'LOW | NORMAL | HIGH' ]
	[ , [ @sensitivity = ] 'PUBLIC | PRIVATE | CONFIDENTIAL' ]
	[ , [ @file_attachments = ] 'file_attachments [ ; ...n ]' ]
	[ , [ @location = ] 'location' ]
	[ , [ @start_time_utc = ] 'start_time_utc' ]
	[ , [ @end_time_utc = ] 'end_time_utc' ]
	[ , [ @timestamp_utc = ] 'timestamp_utc' ]
	[ , [ @method = ] 'PUBLISH | REQUEST | REPLY | CANCEL | ADD | REFRESH | COUNTER | DECLINECOUNTER' ]
	[ , [ @sequence = ] sequence ]
	[ , [ @prod_id = ] 'prod_id' ]
	[ , [ @use_reminder = ] 1 | 0 ]
	[ , [ @reminder_minutes = ] reminder_minutes ]
	[ , [ @require_rsvp = ] 1 | 0 ]
	[ , [ @recipients_role = ] 'REQ-PARTICIPANT | OPT-PARTICIPANT | NON-PARTICIPANT | CHAIR' ]
	[ , [ @copy_recipients_role = ] 'REQ-PARTICIPANT | OPT-PARTICIPANT | NON-PARTICIPANT | CHAIR' ]
	[ , [ @blind_copy_recipients_role = ] 'REQ-PARTICIPANT | OPT-PARTICIPANT | NON-PARTICIPANT | CHAIR' ]
	[ , [ @smtp_servername = ] 'smtp_servername' ]
	[ , [ @port = ] port ]
	[ , [ @enable_ssl = ] 1 | 0 ]
	[ , [ @use_default_credentials = ] 1 | 0 ]
	[ , [ @username = ] 'username' ]
	[ , [ @password = ] 'password' ]
	[ , [ @suppress_info_messages = ] 1 | 0 ]
	[ , [ @event_identifier = ] 'event_identifier' [ OUTPUT ] ]
	[ , [ @ics_contents = ] 'ics_contents' [ OUTPUT ] ]

Arguments

For detailed information about the procedure arguments, please click here.

Result

On success, prints the message “Mail Sent. Event Identifier: %s” (where %s is replaced with the sent @event_identifier), unless @suppress_info_messages is specified as 1 (true).

On Failure, returns an error message specifying the problem.

Examples

A. Send a calendar invitation with RSVP requirement

DECLARE @EventID nvarchar(255)
 
EXEC sp_send_calendar_event
        @from_address = N'[email protected]',
        @recipients = N'[email protected],[email protected]',
        @subject = N'let us meet for pizza!',
        @body = N'<h1>Pizza!</h1><p>Bring your own beer!</p>',
        @body_format = N'HTML',
        @location = N'The Pizza place at Hank and Errison corner',
        @start_time_utc = '2019-07-02 19:00',
        @end_time_utc = '2019-07-02 23:00',
        @timestamp_utc = '2019-03-30 18:00',
        @smtp_server = 'smtp.gmail.com',
        @port = 465,
        @enable_ssl = 1,
        @username = N'[email protected]',
        @password = N'NotActuallyMyPassword',
        @use_reminder = 1,
        @reminder_minutes = 30,
        @require_rsvp = 1,
        @event_identifier = @EventID OUTPUT
 
SELECT EventID = @EventID

B. Cancel the previously sent invitation

EXEC sp_send_calendar_event
        @from_address = N'[email protected]',
        @recipients = N'[email protected],[email protected]',
        @subject = N'let us meet for pizza!',
        @body = N'<h1>Pizza!</h1><p>Bring your own beer!</p>',
        @body_format = N'HTML',
        @location = N'The Pizza place at Hank and Errison corner',
        @start_time_utc = '2019-07-02 19:00',
        @end_time_utc = '2019-07-02 23:00',
        @timestamp_utc = '2019-03-30 18:00',
        @smtp_server = 'smtp.gmail.com',
        @port = 465,
        @enable_ssl = 1,
        @username = N'[email protected]',
        @password = N'NotActuallyMyPassword',
        @require_rsvp = 1,
        @method = 'CANCEL',
        @event_identifier = @EventID OUTPUT
 
SELECT EventID = @EventID

C. Send an automated calendar invitation without RSVP requirement (i.e. participants are auto-accepted)

DECLARE @EventID nvarchar(255)
 
EXEC sp_send_calendar_event
        @from_address = N'[email protected]',
        @recipients = N'[email protected],[email protected]',
        @subject = N'Weekly SLA Shift',
        @body = N'<h1>You are on-call this week!</h1><p>This is an automated message</p>',
        @body_format = N'HTML',
        @location = N'Our offices',
        @start_time_utc = '2019-07-01 00:00',
        @end_time_utc = '2019-07-04 23:59',
        @timestamp_utc = '2019-05-01 00:00',
        @smtp_server = 'smtp.company.com',
        @port = 587,
        @enable_ssl = 1,
        @username = N'[email protected]',
        @password = N'SomethingPassword',
        @use_reminder = 1,
        @reminder_minutes = 300,
        @require_rsvp = 0,
        @event_identifier = @EventID OUTPUT
 
SELECT EventID = @EventID

Remarks

I did my best to align the parameters of this procedure with Microsoft’s sp_send_dbmail procedure. However, since this is a CLR procedure, there are limitations to what can be done. Specifically, it’s impossible to define default values for parameters of type nvarchar(max) and varchar(max), and so I had to create a wrapper procedure in T-SQL instead.

Even though I tried to utilize Microsoft’s Database Mail Profile mechanics, I couldn’t get access to the account passwords (which is probably a good thing), and so the @password parameter becomes mandatory (unless you want to use an empty password or the server’s default network credentials).

I also didn’t implement any functionality involving multiple accounts per profile to be used as “failover” accounts. So only the first account per profile is used.

The project is still in development, so additional features and/or changes may be implemented with time (and you may also suggest your own).

As the time of this writing, version 2.0 beta is the latest release, with a revamped list of parameters and a bucket-load of new features.

Check out the GitHub Issues page for more info about any planned or on-going development.

Contribution & Issue Submission

This is an open-source project, and therefore you may freely submit your own issues and pull requests, if you wish to contribute.

Any contribution is welcome.

You may access the issues at the GitHub Issues page.

License and copyright

This project is licensed under the MIT license agreement.

More info in the license file.

Acknowledgements

This project was based mostly on the following stack overflow discussion: Send email to Outlook with ics meeting appointment

Also used the RFC 2445 iCalendar specification as reference for further improvements and fine-tuning.

See Also

• sp_send_dbmail
• clr_http_request
• clr_wmi_request