Excel服务器标准版实现邮件通知配置.docVIP

Excel服务器的配置: 基本目的是希望当用户有待办的时候能够以邮件的方式进行通知,让用户及时办理. 方法:利用通过SQL 的触发器监控保存待办的表.如果有新的记录.就利用SQL Server2000邮件功能向用户邮箱发送一个通知邮件 一,登陆Excel服务器管理控制台,为每个用户添加邮箱: 二,对Excel服务器数据库添加相应程序 1,在ESsys数据库里建立一个存放邮件发送记录的数据表.可以直接在Sql server的查询分析直接输入下面的代码执行就可以了: if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[SendMail]) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [dbo].[WorkFlow] GO CREATE TABLE [dbo].[WorkFlow] ( [UserId] [int] NULL , [UserName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [Email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [SendMail] [int] NULL , [WiDesc] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 2,建立邮件发送的触发器.当SendMail数据表有新增数据的时候.自动执行发送邮件:脚本如下: CREATE TRIGGER [send_mail] ON [dbo].[workflow] FOR INSERT AS DECLARE @MAIL AS VARCHAR(100) DECLARE @CUST_PO AS VARCHAR(100) DECLARE @PCUST_PO AS VARCHAR(100) DECLARE @MSG AS VARCHAR(200) DECLARE @NAME AS VARCHAR(20) DECLARE @BODY AS VARCHAR(200) DECLARE @UID AS INT DECLARE abc CURSOR SCROLL FOR SELECT email FROM inserted OPEN ABC FETCH FIRST FROM abc into @CUST_PO WHILE (@@FETCH_STATUS = 0) begin SELECT @MSG=inserted.WiDesc, @mail=inserted.sendmail,@UID=inserted.UserId, @name=inserted.UserName FROM ESSys.dbo.CFWorkItems CROSS JOIN inserted WHERE inserted.Email=@CUST_PO AND inserted.WiDesc=ESSys.dbo.CFWorkItems.WiDesc SELECT @PCUST_PO=rtrim(@CUST_PO) SELECT @BODY=RTRIM(@NAME)+，您有待办事宜：“+RTRIM(@MSG)+”，请到/esweb办理！本邮件由系统自动产生，请勿回复。 EXEC Master..xp_sendmail @recipients = @PCUST_PO, @subject = 待办事宜提醒, @message =@BODY --UPDATE ESSys.dbo.CFWiTodo SET ESSys.dbo.CFWiTodo.sendmail=1 WHERE ESSys.dbo.CFWiTodo.UserId=@UID FETCH NEXT FROM abc INTO @CUST_PO END CLOSE abc DEALLOCATE abc 三,对ESsys数据里的CFWiTodo表建立一个触发器.当有新的待办生成的时候,自动的把相关数据插入到SendMail表.代码如下: CREATE TRIGGER [ins_mail] ON [dbo].[CFWiTodo] FOR INSERT AS insert into dbo.SendMail(userid,username,Email,widesc) select a.userid,username,E