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

  • 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

  • no answer ????

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

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

  • 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

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

  • 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'

  • 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'

  • 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

     

  • This was removed by the editor as SPAM

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

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