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

Question

Post reply

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

midan1

SSCertifiable

Points: 5100
More actions
September 27, 2007 at 7:28 am

#178151

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
August 18, 2022 at 7:07 am

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

midan1 SSCertifiable Points: 5100 More actions · Answer 1

midan1

SSCertifiable

Points: 5100

October 1, 2007 at 1:28 pm

#737860

no answer ????

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

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: 12335 More actions · Answer 3

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 More actions · Answer 4

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: 736162 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 Enthusiast Points: 172 More actions · Answer 8

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