none
Can I send an Outlook appointment through sp_send_dbmail?

    Question

  • I've been doing a lot of reseach around this and this is how far I've got. I've cobbled together some code from various sources and so far this is what I have.

    EXEC msdb.dbo.sp_send_dbmail
    
     @recipients     = 'address@server.com'
    
     -- ,@copy_recipients    = 'copy_recipient [ ; ...n ]'
    
     -- ,@blind_copy_recipients = 'blind_copy_recipient [ ; ...n ]'
    
     
    
     -- ,@subject        = 'subject' 
    
     , @body          = 'test'
    
     -- , @body_format     = 'body_format'
    
     -- , @importance     = 'importance'
    
     --- , @sensitivity     = 'sensitivity' 
    
     , @query           = 
    
        '
    
        --
    
        SET NOCOUNT ON;
    
        --
    
        SELECT ''BEGIN:VCALENDAR'' + char(13)+char(10)
    
    				+ ''PRODID:-//Microsoft Corporation//Outlook 11.0 MIMEDIR//EN'' + char(13)+char(10)
    
    				+ ''VERSION:2.0'' + char(13)+char(10)
    
    				+ ''METHOD:PUBLISH'' + char(13)+char(10)
    
    				+ ''BEGIN:VEVENT'' + char(13)+char(10)
    
    				+ ''ORGANIZER:MAILTO:name@server.com'' + char(13)+char(10)
    
        + REPLACE(''DTSTART:{appointmentStart}Z'',''{appointmentStart}'',REPLACE(REPLACE(CONVERT ( varchar(19), a.appointmentStart  , 127 ), ''-'', ''''),'':'','''')) + char(13)+char(10)
    
        + REPLACE(''DTEND:{appointmentFinish}Z'',''{appointmentFinish}'',REPLACE(REPLACE(CONVERT ( varchar(19), a.appointmentFinish  , 127 ), ''-'', ''''),'':'','''')) + char(13)+char(10)
    
        + REPLACE(''LOCATION:{appointmentLocation}'',''{appointmentLocation}'',a.appointmentLocation) + char(13)+char(10)
    
        + ''TRANSP:OPAQUE'' + char(13)+char(10)
    
    				+ ''SEQUENCE:0'' + char(13)+char(10)
    
    				+ ''UID:ABCD1234'' + char(13)+char(10)
    
    				+ ''DTSTAMP:20110111T155247Z'' + char(13)+char(10)
    
        + REPLACE(''SUMMARY:{appointmentDescription}'',''{appointmentDescription}'',a.appointmentDescription) + char(13)+char(10)
    
    				+ ''PRIORITY:5'' + char(13)+char(10)
    
    				+ ''X-MICROSOFT-CDO-IMPORTANCE:1'' + char(13)+char(10)
    
    				+ ''CLASS:PUBLIC'' + char(13)+char(10)
    
    				+ ''BEGIN:VALARM'' + char(13)+char(10)
    
    				+ ''TRIGGER:-PT15M'' + char(13)+char(10)
    
    				+ ''ACTION:DISPLAY'' + char(13)+char(10)
    
    				+ ''DESCRIPTION:Reminder'' + char(13)+char(10)
    
    				+ ''END:VALARM'' + char(13)+char(10)
    
    				+ ''END:VEVENT'' + char(13)+char(10)
    
    				+ ''END:VCALENDAR''
    
        FROM
    
        [SLCSystem2010].[dbo].[tblAppointment] a
    
        '    
    
     , @attach_query_result_as_file  = 1
    
     , @query_result_header      = 0
    
     , @query_result_separator   = 'CHAR(10)+CHAR(13)'
    
     , @exclude_query_output      = 1  
    
     , @query_attachment_filename  = 'appointment.ics'
    
    

    This does send the email through with an ics attached file. But when accepting the appointment it errors

    "This error can appear if you have attempted to save a recurring Lunar appointment in iCalendar format.

    To avoid this error, set the appointment option to Gregorian instead of Lunar."

     

    From the research I've done this seems to be some kind of encoding problem with the email as if i save the file and view it in notepad it looks identical to an appoinment created in outlook and saved and viewed in notepad.

    It's difficult to understand if what I'm trying to do is even possible, if it's not I could save myself a lot of strife.

    Any comments greatly appreciated.

     

    TIA

    Iain

    • Moved by Tom PhillipsModerator Tuesday, January 25, 2011 1:46 PM Possiblty better answer from TSQL forum (From:SQL Server Database Engine)
    Wednesday, January 12, 2011 12:07 PM

Answers

  •  

    Hi a99iainb,

     

    My colleague Qiu Ai Hua just write an article about how to sent appointments to an OutLook Calendar from SQL Server 2005, his method is more simple, you could download from here and have a look:
     

    http://cid-42c6175c7763be85.office.live.com/browse.aspx/.Documents

     

    Hope it helps.

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Wednesday, January 26, 2011 1:47 AM

All replies