how to Sending Appointments to Outlook ics File-from sql server Email

  • midan1

    SSCertifiable

    Points: 5100

    how to send Email from sql server 2005 with Appointments to Outlook ics File

    Sending Appointments to an Outlook Calendar from sql server 2005

    TNX

  • midan1

    SSCertifiable

    Points: 5100

    no answer ????

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

    You would need the format of the Appointment email and then build that in SQL Server. I don't know of a solution off hand for this.

    Is it using Exchange? There was a product (Search sqlexchange) to update Exchange with data from SQL Server.

  • bledu

    SSChampion

    Points: 12275

    my thinking is you can use the outlook programming model so you could probably use the .net runtime. create a dll which has the outlook calendar objects etc.. and add a clr udf to your server.


    Everything you can imagine is real.

  • midan1

    SSCertifiable

    Points: 5100

    what i need is

    only to generate from sql mail the file "ICS" and to send it as email attachment

    can i get any help to do this

    TNX

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

    If you need help to generate an ICS file, you probably need to go to an Exchange or .NET site dealing with mail. Once that is done, we can help with the mailing, but this isn't necessarily the best place for a programming task like that.

  • GrahameHorner

    SSC Journeyman

    Points: 87

    Here is a sample t-sql script; it uses a query to generate an attachment that has the ics format of an appointment;

    hope it helps

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'GrahameHorner@thinkpositive.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)

    + ''PRODID:-//PositiveSolutions//Extranet//EN''

    + CHAR(13)

    + ''VERSION:1.0''

    + CHAR(13)

    + ''METHOD:PUBLISH''

    + CHAR(13)

    + ''BEGIN:VEVENT''

    + CHAR(13)

    + ''CLASS:PUBLIC''

    + CHAR(13)

    + REPLACE(''DESCRIPTION:{appointmentDescription}'',''{appointmentDescription}'',a.appointmentDescription)

    + CHAR(13)

    + REPLACE(''DTEND:{appointmentFinish}Z'',''{appointmentFinish}'',REPLACE(REPLACE(CONVERT ( varchar(19), a.appointmentFinish , 127 ), ''-'', ''''),'':'',''''))

    + CHAR(13)

    + REPLACE(''DTSTART:{appointmentStart}Z'',''{appointmentStart}'',REPLACE(REPLACE(CONVERT ( varchar(19), a.appointmentStart , 127 ), ''-'', ''''),'':'',''''))

    + CHAR(13)

    + REPLACE(''LOCATION:{appointmentLocation}'',''{appointmentLocation}'',a.appointmentLocation)

    + CHAR(13)

    + ''END:VEVENT''

    + CHAR(13)

    + ''END:VCALENDAR''

    FROM

    [Extranet].[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'

  • ADY

    SSC Journeyman

    Points: 76

    I tried to your solution with below script. it executes successfully but no message at recipient end. Can you please me

    ____________________________________________________________________________

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'abc@hotmail.com'

    ,@copy_recipients = 'xyz@hotmail.com'

    ,@blind_copy_recipients = 'KLM@hotmail.com'

    ,@subject = 'Test Meeting'

    ,@body = 'test by adnan'

    -- , @body_format = 'body_format'

    --, @importance = 'importance'

    --- , @sensitivity = 'sensitivity'

    , @query =

    '

    --

    SET NOCOUNT ON;

    --

    SELECT ''BEGIN:VCALENDAR''

    + CHAR(13)

    + ''PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN''

    + CHAR(13)

    + ''VERSION:1.0''

    + CHAR(13)

    + ''METHOD:PUBLISH''

    + CHAR(13)

    + ''BEGIN:VEVENT''

    + CHAR(13)

    + ''CLASS:PUBLIC''

    + CHAR(13)

    + REPLACE(''DESCRIPTION:{appointmentDescription}'',''{appointmentDescription}'',a.appointmentDescription)

    + CHAR(13)

    + REPLACE(''DTEND:{appointmentFinish}Z'',''{appointmentFinish}'',REPLACE(REPLACE(CONVERT ( varchar(19), a.appointmentFinish , 127 ), ''-'', ''''),'':'',''''))

    + CHAR(13)

    + REPLACE(''DTSTART:{appointmentStart}Z'',''{appointmentStart}'',REPLACE(REPLACE(CONVERT ( varchar(19), a.appointmentStart , 127 ), ''-'', ''''),'':'',''''))

    + CHAR(13)

    + REPLACE(''LOCATION:{appointmentLocation}'',''{appointmentLocation}'',a.appointmentLocation)

    + CHAR(13)

    + ''END:VEVENT''

    + CHAR(13)

    + ''END:VCALENDAR''

    FROM

    [tblAppointmnt] 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'

  • Eitan Blumin

    SSC Journeyman

    Points: 90

    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

     

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply