Skip to content
Home » New open source project: Send calendar invites from within SQL Server

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

28 thoughts on “New open source project: Send calendar invites from within SQL Server”

  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?

    1. User Avatar

      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.

  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?

    1. User Avatar

      Hi J,
      That part in the code is creating the entire assembly using pure T-SQL. It’s an alternative to loading the assembly from a DLL file.

      I generated this code by right clicking on the assembly in ssms and selecting CREATE TO – New query window.

      1. Thanks for the quick response. What Dll files are they using? Also does this work with office 365?

        1. User Avatar

          The whole CLR project itself is the main DLL assembly that’s being used. If you’re asking what other assemblies it’s dependent on, then those would be System.Net and System.Data.

          You can find them at the top of the main CS file: https://github.com/MadeiraData/sql-clr-ics/blob/master/sql_clr_ics/clr_send_ics_invite.cs

          And also at the “Reference” elements in the project file: https://github.com/MadeiraData/sql-clr-ics/blob/master/sql_clr_ics/sql_clr_ics.sqlproj#L74-L76

          As for Office 365, I really don’t know.
          I personally tried to use it with O365 but without success.
          Mostly due to the complicated authentication mechanisms there.

  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

    1. User Avatar

      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

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

        1. User Avatar

          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.

          1. 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…

  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

    1. User Avatar

      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.

  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

  6. Hi Nik,

    I’m getting the below error when I try to run the script?

    Msg 6522, Level 16, State 1, Procedure clr_send_ics_invite, Line 0 [Batch Start Line 0]
    A .NET Framework error occurred during execution of user-defined routine or aggregate “clr_send_ics_invite”:
    System.InvalidOperationException: Invalid attempt to read when no data is present.
    System.InvalidOperationException:
    at System.Data.SqlClient.SqlDataReaderSmi.EnsureOnRow(String operationName)
    at System.Data.SqlClient.SqlDataReaderSmi.GetSqlString(Int32 ordinal)
    at StoredProcedures.clr_send_ics_invite(SqlString profile_name, SqlString recipients, SqlString copy_recipients, SqlString blind_copy_recipients, SqlString from_address, SqlString reply_to, SqlString subject, SqlString body, SqlString body_format, SqlString importance, SqlString sensitivity, SqlString file_attachments, SqlString location, SqlDateTime start_time_utc, SqlDateTime end_time_utc, SqlDateTime timestamp_utc, SqlString method, SqlInt32 sequence, SqlString prod_id, SqlBoolean use_reminder, SqlInt32 reminder_minutes, SqlBoolean require_rsvp, SqlString recipients_role, SqlString copy_recipients_role, SqlString blind_copy_recipients_role, SqlString smtp_servername, SqlInt32 port, SqlBoolean enable_ssl, SqlBoolean use_default_credentials, SqlString username, SqlString password, SqlBoolean suppress_info_messages, SqlString& event_identifier, SqlString& ics_contents)

    Can you point me in the right direction?

    1. User Avatar

      Hi Blair,

      The error is: “Invalid attempt to read when no data is present.”
      Its source is a section in the code that reads from a SqlDataReader using the GetSqlString function.

      In the source code, the only sections that query from a SqlDataReader are the ones that try to retrieve data from DB Mail system tables to get mail profile settings.

      I’m not sure what exactly could cause this error since there’s supposed to be a preliminary check whether rows were returrned or not.

      I think, in such a case when there are records returned but this error is thrown, it means that the field being retrieved is NULL.

      This is definitely a bug on my part, as I should’ve added a nullability check on the database fields before retrieval.

      For now, try running the query below and see which field(s) are NULL:

      SELECT TOP 1 a.email_address, a.replyto_address, s.servername, s.port, s.enable_ssl, s.use_default_credentials, s.username
      FROM [msdb].[dbo].[sysmail_profile] AS p
      INNER JOIN [msdb].[dbo].[sysmail_profileaccount] AS pa
      ON p.profile_id = pa.profile_id
      AND pa.sequence_number >= @Seq
      INNER JOIN [msdb].[dbo].[sysmail_account] AS a
      ON pa.account_id = a.account_id
      INNER JOIN [msdb].[dbo].[sysmail_server] AS s
      ON p.profile_id = s.account_id
      WHERE p.name = @ProfileName
      ORDER BY pa.sequence_number ASC

      Either update your DBMail profile settings to fill the missing details, or provide the missing details using the procedure parameters.

      1. Thanks Eitan,

        I can’t see anythign that is NULL. What is the parameter @seq so I can run the script

        Regards,

        Blair

      2. Ok, I created a profile with no NULL

        Msg 6522, Level 16, State 1, Procedure clr_send_ics_invite, Line 0 [Batch Start Line 0]
        A .NET Framework error occurred during execution of user-defined routine or aggregate “clr_send_ics_invite”:
        System.InvalidOperationException: Invalid attempt to read when no data is present.
        System.InvalidOperationException:
        at System.Data.SqlClient.SqlDataReaderSmi.EnsureOnRow(String operationName)
        at System.Data.SqlClient.SqlDataReaderSmi.GetSqlString(Int32 ordinal)
        at StoredProcedures.clr_send_ics_invite(SqlString profile_name, SqlString recipients, SqlString copy_recipients, SqlString blind_copy_recipients, SqlString from_address, SqlString reply_to, SqlString subject, SqlString body, SqlString body_format, SqlString importance, SqlString sensitivity, SqlString file_attachments, SqlString location, SqlDateTime start_time_utc, SqlDateTime end_time_utc, SqlDateTime timestamp_utc, SqlString method, SqlInt32 sequence, SqlString prod_id, SqlBoolean use_reminder, SqlInt32 reminder_minutes, SqlBoolean require_rsvp, SqlString recipients_role, SqlString copy_recipients_role, SqlString blind_copy_recipients_role, SqlString smtp_servername, SqlInt32 port, SqlBoolean enable_ssl, SqlBoolean use_default_credentials, SqlString username, SqlString password, SqlBoolean suppress_info_messages, SqlString& event_identifier, SqlString& ics_contents)
        .

          1. There only NULL I can see in there is timeout for that profile

  7. Hi,
    This works great but Is there a option to send recurrence email invite? if so, could you please share the sample code. Thanks for your time and help in advance.

    1. User Avatar

      Hi Lalli, thank you for your comment.
      At this time, this CLR procedure doesn’t natively support recurring events.

      HOWEVER, it does support custom ICS code.
      So, technically, you could generate your own ICS content using the @ics_contents parameter, including the necessary RRULE tag used for defining recurring events.

      1. Thanks for the quick response. is it possible for you to provide the sample code to implement this functionality.

Leave a Reply

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