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).

This means, by the way, that this solution obviously cannot work in Azure SQL Databases.

EDIT: I’ve already created a new release which uses a signed certificate as a safer alternative to the TRUSTWORTHY setting.

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'the_organizer@gmail.com',
@recipients = N'someone@gmail.com,otherguy@outlook.com',
@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'the_organizer@gmail.com',
@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'the_organizer@gmail.com',
@recipients = N'someone@gmail.com,otherguy@outlook.com',
@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'the_organizer@gmail.com',
@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'sla_bot@company.com',
@recipients = N'employee1@company.com,employee2@company.com',
@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'sla_bot@company.com',
@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

16 comments

  1. I ran your setup procedure, but am getting the following error message:

    Creating Assembly [dbo].[sql_clr_ics]…
    Creating [dbo].[clr_send_ics_invite]…
    Msg 156, Level 15, State 1, Procedure clr_send_ics_invite, Line 2
    Incorrect syntax near the keyword ‘NULL’.
    Creating [dbo].[sp_send_calendar_event]…
    The module ‘sp_send_calendar_event’ depends on the missing object ‘dbo.clr_send_ics_invite’. The module will still be created; however, it cannot run successfully until the object exists.

    How do I get past this?

    Like

    • Hi and thank you for your comment. Based on the error you’re getting, there’s an incorrect usage of NULL in the command that creates [dbo].[clr_send_ics_invite]

      However, I don’t see such a thing in the current version of the script, so maybe you’re using an outdated version.

      Please try the most recent version from the latest release or from the master branch.

      Like

  2. Can you explain the

    CREATE ASSEMBLY [sql_clr_ics]
    AUTHORIZATION [dbo]
    FROM

    ALTER ASSEMBLY [sql_clr_ics]
    DROP FILE ALL
    ADD FILE FROM

    What is the code your applying mean?

    Like

  3. Hi Eitan

    Great work here, very impressive. Thanks so much

    A few questions…

    Will using local time instead of UTC work? If so how would I input it?

    The TimeStamp – is this the time sent? Does it need to be UTC?

    Also the CancelEvent – does this find the original appointment by the times only?

    Thanks – hard to believe that such a common and useful tool is not easy to find!

    Cheers

    Martin

    Like

    • Hi Martin,
      Thank you for your questions.

      Answers below:

      Will using local time instead of UTC work?

      – I’m afraid you’ll have no choice but to work with UTC here. Fortunately, SQL server has some functionality in place to allow you to convert times from UTC and back. So, you should be able to add some wrapper code to convert non-UTC times to UTC before executing the procedure.

      The timestamp – is this the time sent?

      – Yes, it’s the time sent and it must be in UTC.

      Also the CancelEvent – doors this find the original appointment by the times only?

      – No, actually. It’s supposed to find the appointment based on the EventID with a combination of a sequence property. Unfortunately though, there’s currently some kind of bug with cancelling events which I was yet to find a solution for.

      You may find additional documentation at the GitHub repository for the project:

      https://github.com/MadeiraData/sql-clr-ics

      Thank you for the kind words,
      Eitan

      Like

      • Thanks for your quick reply.. am always concerned whether those conversions take Daylight Saving correctly into account…

        am currently using SQL 2014 – AT Time ZONE only works on and above 2016

        I guess I should ask people to manually cancel the event from the calendar then?

        Thanks again

        Martin

        Like

      • Well, that’s the nice thing about UTC – there is no Daylight Savings to take into account 😉

        Unfortunately, there’s no easy solution for this without using AT TIME ZONE. I did find an open-source project trying to do it, though: https://github.com/mattjohnsonpint/SqlServerTimeZoneSupport

        As for event cancellation, yes. That’s what I find myself doing right now as I was unable to find a solution for the bug and no one was able to assist as of yet.

        Like

      • Thanks — I only need to worry about 1 time zone – so it may be easier to make my own tables on when to account for Daylight Saving and just add in the differential…

        Like

  4. Have made a simple solution for managing UTC/Local time zone conversions…

    However – if one wants to book say a tennis court and you make the booking while overseas in a different time zone you will receive the ics file which will show the time (in the calendar) in the timezone the computer is registered to.

    I also noticed in this ics file below the timezones are local (I think)…

    Your thoughts?

    BEGIN:VCALENDAR
    VERSION:2.0
    BEGIN:VEVENT
    DTSTART:20210902T113000
    DTEND:20210902T123000
    LOCATION;ENCODING=QUOTED-PRINTABLE:Rushcutters Bay Tennis and Kiosk
    DESCRIPTION:Court Hire 60 Minutes session scheduled with Rushcutters Court 1
    SUMMARY;ENCODING=QUOTED-PRINTABLE:Court Hire 60 Minutes
    PRIORITY:3
    END:VEVENT
    END:VCALENDAR

    Like

    • Technically, there are ways to specify a time zone in vcalendar, but it definitely doesn’t look like this. It’s a lot more complicated.

      A better solution would be to convert whatever it is you’re working with into UTC and only then use the procedure to send the invite.

      Most programming languages provide functionalities to convert any local time to UTC time zone and back. So, my suggestion to you is to do this at the app level so that the time will be converted to UTC even before it reaches the database.

      Like

  5. Hi Eitan, Thanks for your good work. I could send email directly as invitation. Could you provide an example how to send a mail with .ics file as attachment, like it is done with msdb.dbo.sp_send_dbmail
    Thanks
    Nik

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.