1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
|
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'prenom.nom@xxx.fr'
,@subject = 'Test mise à jour calendrier outlook'
,@body = 'Test mise à jour calendrier outlook'
-- , @body_format = 'body_format'
--, @importance = 'importance'
--- , @sensitivity = 'sensitivity'
, @query =
'
--
SET NOCOUNT ON;
--
SELECT ''BEGIN:VCALENDAR''
+ CHAR(13) + CHAR(10)
+ ''PRODID:-//Microsoft Corporation//Outlook 15.0 MIMEDIR//EN''
+ CHAR(13) + CHAR(10)
+ ''VERSION:2.0''
+ CHAR(13) + CHAR(10)
+ ''METHOD:PUBLISH''
+ CHAR(13) + CHAR(10)
+ ''X-MS-OLK-FORCEINSPECTOROPEN:TRUE''
+ CHAR(13) + CHAR(10)
+ ''BEGIN:VTIMEZONE''
+ CHAR(13) + CHAR(10)
+ ''TZID:Romance Standard Time''
+ CHAR(13) + CHAR(10)
+ ''BEGIN:STANDARD''
+ CHAR(13) + CHAR(10)
+ ''DTSTART:16011028T030000''
+ CHAR(13) + CHAR(10)
+ ''RRULE:FREQ=YEARLY;BYDAY=-1SU;BYMONTH=10''
+ CHAR(13) + CHAR(10)
+ ''TZOFFSETFROM:+0200''
+ CHAR(13) + CHAR(10)
+ ''TZOFFSETTO:+0100''
+ CHAR(13) + CHAR(10)
+ ''END:STANDARD''
+ CHAR(13) + CHAR(10)
+ ''BEGIN:DAYLIGHT''
+ CHAR(13) + CHAR(10)
+ ''DTSTART:16010325T020000''
+ CHAR(13) + CHAR(10)
+ ''RRULE:FREQ=YEARLY;BYDAY=-1SU;BYMONTH=3''
+ CHAR(13) + CHAR(10)
+ ''TZOFFSETFROM:+0100''
+ CHAR(13) + CHAR(10)
+ ''TZOFFSETTO:+0200''
+ CHAR(13) + CHAR(10)
+ ''END:DAYLIGHT''
+ CHAR(13) + CHAR(10)
+ ''END:VTIMEZONE''
+ CHAR(13) + CHAR(10)
+ ''BEGIN:VEVENT''
+ CHAR(13) + CHAR(10)
+ ''CLASS:PRIVATE''
+ CHAR(13) + CHAR(10)
+ ''DESCRIPTION:'' + a.Objet
+ CHAR(13) + CHAR(10)
+ ''CREATED:'' + a.DateStamp
+ CHAR(13) + CHAR(10)
+ ''DTSTAMP:'' + a.DateStamp
+ CHAR(13) + CHAR(10)
+ ''DTEND;TZID="Romance Standard Time":'' + a.DateFin
+ CHAR(13) + CHAR(10)
+ ''DTSTART;TZID="Romance Standard Time":'' + a.DateDebut
+ CHAR(13) + CHAR(10)
+ ''LOCATION:'' + a.Lieu
+ CHAR(13) + CHAR(10)
+ ''ATTENDEE;CN="'' + a.DestiCompte + ''";RSVP=TRUE:mailto:'' + a.DestiMail
+ CHAR(13) + CHAR(10)
+ ''ORGANIZER;CN="'' + a.EmetteurCompte + ''";RSVP=TRUE:mailto:'' + a.EmetteurMail
+ CHAR(13) + CHAR(10)
+ ''UID:0''
+ CHAR(13) + CHAR(10)
+ ''SUMMARY;LANGUAGE=fr:'' + a.Objet
+ CHAR(13) + CHAR(10)
+ ''END:VEVENT''
+ CHAR(13) + CHAR(10)
+ ''END:VCALENDAR''
+ CHAR(13) + CHAR(10)
FROM
(select Objet,Description,Lieu,DestiCompte,DestiMail,EmetteurCompte,EmetteurMail,
REPLACE(REPLACE(CONVERT ( varchar(19), V.Debut , 127 ), ''-'', ''''),'':'','''') + ''Z'' as DateStamp,
REPLACE(REPLACE(CONVERT ( varchar(19), V.Fin , 127 ), ''-'', ''''),'':'','''') as DateFin,
REPLACE(REPLACE(CONVERT ( varchar(19), V.Debut , 127 ), ''-'', ''''),'':'','''') as DateDebut
from (
SELECT
''Convocation médicale'' as Objet,
''Convocation médicale\n\nMettez à jour votre calendrier\n'' as Description,
convert(smalldatetime,''13/09/2017 10:00'') as Creation,
convert(smalldatetime,''13/09/2017 10:00'') as Debut,
convert(smalldatetime,''13/09/2017 11:00'') as Fin,
''Bat médical'' as Lieu,
''Nom Prenom'' as DestiCompte,
''prenom.nom@xxx.fr'' as DestiMail,
''Nom Prenom'' as EmetteurCompte,
''prenom.nom@xxx.fr'' as EmetteurMail ) as V
) as 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 = 'convocation.ics' |
Partager