Send meeting invitations using Database Mail

http://social.microsoft.com/Forums/it-IT/3ba417d6-6548-48ed-9e9f-1a6549edc569

Hello everyone

Here is an example showing how to send a meeting invitation using Database Mail.
Create a CLR stored procedure to generate an. ics file:
1. In Visual Studio, create a Visual C # SQL CLR Database Project, see:

2. To add a database reference, see:

3. To create a CLR stored procedure named Clroutlook, see:

4. To create an. ics file using the System.IO.File.WriteAllLines method, see:

[Microsoft.SqlServer.Server.SqlProcedure]  public static void Clroutlook (string meetinglocation,string meetingsubject,string meetingdescription,datetime Begindate,datetime EndDate)  {    //Put Your code here    string[] contents = {"Begin:vcalendar",               "prodid:-/ /flo inc.//flosoft//en ",               " Begin:vevent ",               " Dtstart: "+ begindate.touniversaltime (). ToString ("Yyyymmdd\\thhmmss\\z"),                "dtend:" + enddate.touniversaltime (). ToString ("Yyyymmdd\\thhmmss\\z"),                "Location:" + meetinglocation,               "DESCRIPTION; Encoding=quoted-printable: "+ meetingdescription,               " SUMMARY: "+ meetingsubject," Priority:3 ",               " End:vevent " , "End:vcalendar"};    System.IO.File.WriteAllLines (@ "E:\Meeting\OutLookMeeting.ics", contents);  }

Note: We need to use the System.IO namespace, in SQL Server, CLR integration does not support this namespace, and when we deploy the CLR stored procedure, we can open the project file properties and set permissions such as:

5. We can also use T-SQL statements to register the Assembly, see the following statement:

--set the database to trustworthy Modealter database testset trustworthy on; Gocreate ASSEMBLY clroutlookfrom ' E:\CLR\OutLook\CLROutLook\CLROutLook\bin\Debug\CLROutLook.dll ' with Permission_ SET = UNSAFE

6. Create a stored procedure, see the following statement:

CREATE PROCEDURE [dbo]. [Clroutlook] @MeetingLocation [nvarchar] (4000), @MeetingSubject [nvarchar] (4000), @MeetingDescription [nvarchar] ( 4000), @BeginDate [datetime], @EndDate [Datetime]with EXECUTE as Callerasexternal NAME [Clroutlook]. [StoredProcedures]. [Clroutlook] GO

To configure Data messages:

1. configuration file and email account:

2. Send mail using sp_send_dbmail:

--create A. ICS Filedeclare @MeetingLocation NVARCHAR (a) DECLARE @MeetingSubject NVARCHAR (a) DECLARE @ Meetingdescription NVARCHAR ($) DECLARE @BeginDate datetimedeclare @EndDate datetimeset @MeetingLocation = ' 3106 ' SET @ meetingsubject= ' SQL Replication Training ' Set @MeetingDescription = ' Replication is a SET of technologies for copying and dis tributing data and database objects from one database to another and then synchronizing between databases to maintain cons Istency ' Set @BeginDate = ' 2010-12-25 10:00:000 ' Set @EndDate = ' 2010-12-25 11:00:000 ' EXEC dbo. Clroutlook @MeetingLocation, @MeetingSubject, @MeetingDescription, @BeginDate, @EndDateGO--send emailexec msdb.dbo.sp _send_dbmail   @profile_name = ' testprofile ',   @recipients = ' [email protected] ',   @subject = ' SQL Replication Training invitation ',   @file_attachments = ' E:\Meeting\OutLookMeeting.ics '

The result is:


Tips for Database Mail:
1. For more information on Database Mail, please refer to:
Database Mail
http://msdn.microsoft.com/zh-cn/library/ms175887 (v=sql.90). aspx

Thank you.