22 Sep 11:22

431db60

V2.3 beta Latest

Latest

sp_send_calendar_event

Send Calendar Event / Appointment Invitations (iCal formatted file) from within SQL Server using a CLR stored procedure.

Background

Click here for some background information about this project.

Prerequisites

The instance configuration clr enabled must be turned on, and it will be turned on automatically during the pre-deployment phase of this project.

The CLR assembly in this project requires the UNSAFE permission set.

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.

Release Notes

This version introduces a few error handling improvements.
It also implements backward-compatibility for older SQL Server versions 2005 and 2008.

The standalone installation script no longer requires the TRUSTWORTHY setting to be turned on.

However, you must place the assembly DLL in a location accessible to the SQL Server instance.
Set the PathToSignedDLL SQLCMD variable to the correct DLL path.

For more information and documentation, please visit the repository page:

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

Assets 7

16 Apr 07:04

EitanBlumin

v2.1-beta

18f723d

V2.1 beta

sp_send_calendar_event

Send Calendar Event / Appointment Invitations (iCal formatted file) from within SQL Server using a CLR stored procedure.

Background

Click here for some background information about this project.

Prerequisites

The instance configuration clr enabled must be turned on.

The CLR assembly in this project requires the UNSAFE permission set.

Installation

Release Notes

This version introduces some deployment improvements.

The standalone installation script no longer requires the TRUSTWORTHY setting to be turned on.

However, you must place the assembly DLL in a location accessible for the SQL Server instance.
Set the PathToSignedDLL SQLCMD variable to the correct DLL path.

For more information and documentation, please visit the repository page:

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

Assets 7

19 Jun 14:04

EitanBlumin

v2.0-beta

6fc7c50

v2.0 beta

sp_send_calendar_event

Send Calendar Event / Appointment Invitations (iCal formatted file) from within SQL Server using a CLR stored procedure.

Background

Click here for some background information about this project.

Prerequisites

The CLR assembly in this project can only be deployed to a database with the trustworthy setting on, due to the assembly requiring the UNSAFE permission set.

Installation

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

[ @profile_name = ] 'profile_name'

Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_calendar_event checks whether @from_address was specified. If not, it uses the default public profile for the msdb database. If @from_address wasn't specified, and there is no default public profile for the database, @profile_name must be specified.

[ @recipients = ] 'recipients [ ; ...n ]'

Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type nvarchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_calendar_event returns an error. This parameter maps to the ATTENDEE property of the iCal spec.

[ @copy_recipients = ] 'copy_recipients [ ; ...n ]'

Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type nvarchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_calendar_event returns an error. This parameter maps to the ATTENDEE property of the iCal spec.

[ @blind_copy_recipients = ] 'blind_copy_recipients [ ; ...n ]'

Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type nvarchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_calendar_event returns an error. This parameter maps to the ATTENDEE property of the iCal spec.

[ @from_address = ] 'from_address'

Is the value of the 'from address' of the email message, and the organizer of the calendar meeting. This is an optional parameter used to override the settings in the mail profile (or if no mail profile was specified). This parameter is of type nvarchar(max). If no parameter is specified, the default is NULL. This parameter maps to the ORGANIZER property of the iCal spec.

[ @reply_to = ] 'reply_to'

Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile (or if no mail profile was specified). This parameter is of type nvarchar(max). If no parameter is specified, the default is NULL.

[ @subject = ] 'subject'

Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Meeting'. This parameter maps to the SUMMARY property of the iCal spec.

[ @body = ] 'body'

Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL. This parameter maps to the DESCRIPTION property of the iCal spec.

[ @body_format = ] 'TEXT | HTML'

Is the format of the message body. The parameter is of type varchar(20). When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

TEXT
HTML

Defaults to TEXT.

[ @importance = ] 'LOW | NORMAL | HIGH'

Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:

Low
Normal
High

Defaults to Normal.

The parameter is implemented using the System.Net.Mail.MailPriority enum, and maps to the PRIORITY property of the iCal spec, based on a CUA with a three-level priority scheme.

[ @sensitivity = ] 'PUBLIC | PRIVATE | CONFIDENTIAL'

Is the sensitivity classification of the message. The parameter is of type nvarchar(12). The parameter may contain one of the following values, as per the CLASS property of the iCal spec:

Public
Private
Confidential

Defaults to Public.

[ @file_attachments = ] 'file_attachments [ ; ...n ]'

Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

[ @location = ] 'location'

Is the location of the calendar meeting. The parameter is of type nvarchar(255), with a default of NULL. The parameter maps to the LOCATION property of the iCal spec.

[ @start_time_utc = ] 'start_time_utc'

Is the start time of the calendar meeting, in UTC. The parameter is of type datetime. If the parameter is not specified, it defaults to @timestamp_utc + 5 hours. The parameter maps to the DTSTART property of the iCal spec

[ @end_time_utc = ] 'end_time_utc'

Is the end time of the calendar meeting, in UTC. The parameter is of type datetime. If the parameter is not specified, it defaults to @start_time_utc + 1 hour. The parameter maps to the DTEND property of the iCal spec.

[ @timestamp_utc = ] 'timestamp_utc'

Is the date and time when the calendar event was created, in UTC. The parameter is of type datetime. If the parameter is not specified, it defaults to current UTC time. The parameter maps to the DTSTAMP property of the iCal spec.

Is the method of the calendar event message. The parameter is of type nvarchar(14). The parameter may contain one of the following values, as per the METHOD property of the iCalendar Transport-independent Interoperability Protocol (iTIP):

PUBLISH
REQUEST
REPLY
CANCEL
ADD
REFRESH
COUNTER
DECLINECOUNTER

Defaults to REQUEST.

[ @sequence = ] sequence

Is the sequence of the calendar event message. The parameter is of type int, with a default of 0. Unless @method is specified as 'CANCEL', in which case the default would be 1. Proper usage of this parameter is important when updating existing calendar events, since each consecutive update must have a higher sequence number than the one before it. This parameter maps to the [SEQUENCE property of the ...

Assets 3

20 May 15:22

EitanBlumin

v1.0

4b31145

v1.0

sql_clr_ics

Send Calendar Invites from within SQL Server using a CLR stored procedure.

Prerequisites

The CLR assembly in this project can only be deployed to a database with the trustworthy setting on, due to the assembly requiring the UNSAFE permission set.

Installation

Usage

The CLR stored procedure clr_send_ics_invite accepts the following parameters:

	@from [nvarchar](4000),
	@to [nvarchar](4000),
	@cc [nvarchar](4000) = null,
	@reply_to [nvarchar](4000) = null,
	@subject [nvarchar](4000),
	@body [nvarchar](4000) = null,
	@location [nvarchar](4000) = null,
	@start_time_utc [datetime] = null,
	@end_time_utc [datetime] = null,
	@timestamp_utc [datetime] = null,
	@smtp_server [nvarchar](4000) = null,
	@port [int] = 25,
	@use_ssl [bit] = 0,
	@username [nvarchar](4000) = null,
	@password [nvarchar](4000) = null,
	@use_reminder [bit] = 1,
	@reminder_minutes [int] = 15,
	@require_rsvp [bit] = 0,
	@cancel_event_identifier [uniqueidentifier] = null,
	@event_identifier [uniqueidentifier] = null OUTPUT,
	@suppress_info_messages [bit] = 0

Parameter	Type	Default	Description
`@from`	nvarchar(4000)	no default	Must be a valid single e-mail address from which the invite will be sent.
`@to`	nvarchar(4000)	no default	Accepts a list of e-mail addresses (at least one) to be invited as required partisipants, separated by either a comma or a semicolon.
`@cc`	nvarchar(4000)	null	Optional parameter. Accepts a list of e-mail addresses (at least one) to be used as CC, separated by either a comma or a semicolon.
`@reply_to`	nvarchar(4000)	null	Optional parameter. Accepts an e-mail address to be used as the Reply To address (if different from the `@from` address.
`@subject`	nvarchar(4000)	no default	Mandatory parameter. A text string to be used as the meeting / e-mail's subject.
`@body`	nvarchar(4000)	null	Optional parameter. A text string to be used as the e-mail's HTML body.
`@location`	nvarchar(4000)	null	Optional parameter. Sets the location for the meeting.
`@start_time_utc`	datetime	UTC now + 5 hours	Optional parameter. Sets the start time (in UTC) of the meeting. If not specified, by default will be set as UTC now + 5 hours.
`@end_time_utc`	datetime	@start_time_utc + 1 hour	Optional parameter. Sets the end time (in UTC) of the meeting. If not specified, by default will be set as `@start_time_utc` + 1 hour.
`@timestamp_utc`	datetime	UTC now	Optional parameter. Sets the DTSTAMP section of the iCal (usually used for consistent updating of meeting invites). If not specified, by default will be set as UTC now.
`@smtp_server`	nvarchar(4000)	localhost	Optional parameter. Sets the SMTP host name to be used for sending the e-mail. If not specified, by default will be set as "localhost".
`@port`	int	25	Optional parameter. Sets the SMTP port to be used for sending the e-mail. If not specified, by default will be set as 25.
`@use_ssl`	bit	0	Optional parameter. Sets whether to use SSL authentication for the SMTP server. If not specified, by default will be set as 0 (false).
`@username`	nvarchar(4000)	null (use current Network Credentials)	Optional parameter. Sets the username to use when authenticating against the SMTP server. If not specified, by default the current Network Credentials will be used (of the SQL Server service).
`@password`	nvarchar(4000)	empty password	Optional parameter. Sets the password to use when authenticating against the SMTP server. Only used when `@username` is also specified. By default, will use empty password.
`@use_reminder`	bit	1	Optional parameter. Sets whether to set a reminder for the meeting. By default is set to 1 (true).
`@reminder_minutes`	int	15	If `@use_reminder` is enabled, this parameter will be used for setting the reminder time in minutes. By default is set to 15.
`@require_rsvp`	bit	0	If set to 0 (false), then participants will not be required to respond with RSVP, and their participation is automatically set as ACCEPTED. If set to 1 (true), then participants will be required to respond with RSVP, and their participation is automatically set as NEEDS-ACTION. By default set to 0 (false).
`@cancel_event_identifier`	uniqueidentifier	null	You may specify a value for this parameter, if you want to cancel an event that you've already sent. Use the corresponding event's identifier.
`@event_identifier`	uniqueidentifier	null	Output parameter. Returns the event's GUID, which can later be used for cancellation. If `@cancel_event_identifier` was specified, the same GUID will be returned.
`@suppress_info_messages`	bit	0	If set to 0, an informational message will be printed upon successful delivery of the invitation ( ex. "Mail Sent. Event Identifier: 1234-1234-1234-1234" ). If set to 1, this message will not be printed. By default is set to 0 (false).

License and copyright

This project is copyrighted by Eitan Blumin, and licensed under the MIT license agreement.

More info in the license file.

Acknowledgements

This project was based mostly on the following stack overflow discussion:

https://stackoverflow.com/questions/22734403/send-email-to-outlook-with-ics-meeting-appointment

Also used the iCal specification for further improvements:

https://www.kanzaki.com/docs/ical/

Assets 3

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sp_send_calendar_event

Background

Prerequisites

Installation

Release Notes

sp_send_calendar_event

Background

Prerequisites

Installation

Release Notes

sp_send_calendar_event

Background

Prerequisites

Installation

Syntax

Arguments

sql_clr_ics

Prerequisites

Installation

Usage

License and copyright

Acknowledgements

Releases: MadeiraData/sql-clr-ics

V2.3 beta

sp_send_calendar_event

Background

Prerequisites

Installation

Release Notes

V2.1 beta

sp_send_calendar_event

Background

Prerequisites

Installation

Release Notes

v2.0 beta

sp_send_calendar_event

Background

Prerequisites

Installation

Syntax

Arguments

v1.0

sql_clr_ics

Prerequisites

Installation

Usage

License and copyright

Acknowledgements