3

For example, in health care, every employee gets a PPD test (they jab your arm with something) and you have to go back in 48 hours to see if you've sprouted an extra head.

Everybody forgets.

The nurses document in their EHR that they jabbed the patient, and I can send out an email reminding the employee to go check for their extra head, but I'd rather make it a calendar invite.

Question

How would you set up an outlook calendar appointment from SQL Server?

0
6

(I dont have outlook to test it .. so the code is not tested by me)

Borrowed from : Send Outlook Calendar Requests in Email

EXEC msdb.dbo.sp_send_dbmail    
  @recipients ='<your email address>'    
  ,@subject = 'iCalendar Example'    
  , @query = 'SET NOCOUNT ON;SELECT ''BEGIN:VCALENDAR''+ CHAR(13)+ ''VERSION:2.0''+ CHAR(13)+ ''METHOD:PUBLISH''+ CHAR(13)+ ''BEGIN:VEVENT''+ CHAR(13)+ ''CLASS:PUBLIC''+ CHAR(13)+ ''DESCRIPTION:This is the iCalendar example Most client application support it''+ CHAR(13)+ ''SUMMARY:iCalendar''+ CHAR(13)+ ''DTEND:20150325T110000''+ CHAR(13)+ ''DTSTART:20150325T120000''+ CHAR(13)+ ''LOCATION:My Office''+ 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 = '123.ics'  
3
  • 1
    This method will not work because mail clients such as Outlook won't recognize the message as a calendar invitations, due to incorrect message headers. The solution must be implemented using something that can modify message headers before sending it, and the only option is CLR. I've recently developed an open-source solution in CLR for sending calendar invitations (ical / ics) from within SQL Server. This works: github.com/EitanBlumin/sql_clr_ics – Eitan Blumin May 22 '19 at 13:12
  • Do you get any specific error ? – Kin Shah May 22 '19 at 13:37
  • No, it doesn't show any error. What simply happens is that the invitation is received as a regular e-mail message + the ics file as an attachment. But it's not recognized as a calendar event (i.e. the Accept, Tentative, Decline buttons don't show up). – Eitan Blumin May 23 '19 at 14:42
2

I've recently developed an open-source solution in CLR for sending calendar invitations (ical / ics) from within SQL Server.

This works:

https://github.com/EitanBlumin/sql_clr_ics

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.