调用方法:

测验邮件标题’,’这里是邮件内容,推三、使用CDOSYS 微软现已在 Windows
二〇〇四、Windows XP 以致 Windows 2004 中淘汰了
CDONTS,所以采纳CDOSYS是眼前最好的缓慢解决方案。使用CDOSYS能够选拔远程的SMTP服务器来发送邮件,大家通过测验163果壳网的无需付费邮箱,能够健康发送邮件,相应的蕴藏进程如下:

declare @dbname varchar(50State of Qatarset @dbname = ‘dbtest’ –设置数据库名declare
@filename nvarchar(100State of Qatardeclare @time datetimeset @time = getdate(卡塔尔set
@filename=
‘D:数据库自动备份’+@dbname+substring(replace(replace(replace(CONVERT(varchar,
@time, 120 卡塔尔(قطر‎,’-‘,”State of Qatar,’ ‘,”卡塔尔,’:’,”State of Qatar,1,14 卡塔尔+’.bak’–print
@filenameBACKUP DATABASE dbtest TO DISK = @filename WITH NOINIT,
NOUNLOAD, NAME = N’BIS_data_backup’, NOSKIP , STATS = 10, NOFORMAT

–剖断出错IF @hr 0BEGIN EXEC sp_OAGetErrorInfo @object print ‘failed’
return @objectENDPRINT ‘success’EXEC @hr = sp_OADestroy @objectGO

EXEC @hr = sp_OAMethod @object, ‘Configuration.Fields.Update’, nullEXEC
@hr = sp_OASetProperty @object, ‘To’, @ToEXEC @hr = sp_OASetProperty
@object, ‘Bcc’, @BccEXEC @hr = sp_OASetProperty @object, ‘From’,
@smtpusernameEXEC @hr = sp_OASetProperty @object, ‘Subject’, @Subject

Declare @smtpserver varchar(50State of Qatar –SMTP服务器地址Declare @smtpusername
varchar(50State of Qatar –SMTP服务器客户名Declare @smtpuserpassword varchar(50)–SMTP服务器密码set @smtpserver = ‘smtp.163.com’set @smtpusername =
‘yourname@163.com’ –这里设置成你的163邮箱客户名set @smtpuserpassword =
‘password’ –这里设置成你的163信箱密码Declare @object int Declare @hr
int

–上边三条语句是smtp验证,假使服务器须求表达,则应当要那三句,你须求改进顾客名和密码EXEC
@hr = sp_OASetProperty @object, ‘Configuration.fields().Value’,’1′ EXEC
@hr = sp_OASetProperty @object,
‘Configuration.fields().Value’,@smtpusernameEXEC @hr = sp_OASetProperty
@object, ‘Configuration.fields().Value’,@smtpuserpassword

EXEC @hr = sp_OASetProperty @object, ‘Configuration.fields().Value’,’2′
EXEC @hr = sp_OASetProperty @object, ‘Configuration.fields().Value’,
@smtpserver

代码如下复制代码

–上面获取备份之后文件的大大小小declare @size intdeclare @sizeM decimal (5,
2卡塔尔

CREATE PROCEDURE [dbo]永利皇宫登录网址 ,.[sp_send_cdontsmail] @From varchar(100),@To
varchar(100),@Subject varchar(100),@Body varchar(4000),@CC varchar(100)
= null,@BCC varchar(100) = nullASDeclare @MailID intDeclare @hr intEXEC
@hr = sp_OACreate ‘CDONTS.NewMail’, @MailID OUTEXEC @hr =
sp_OASetProperty @MailID, ‘From’,@FromEXEC @hr = sp_OASetProperty
@MailID, ‘Body’, @BodyEXEC @hr = sp_OASetProperty @MailID,
‘BCC’,@BCCEXEC @hr = sp_OASetProperty @MailID, ‘CC’, @CCEXEC @hr =
sp_OASetProperty @MailID, ‘Subject’, @SubjectEXEC @hr =
sp_OASetProperty @MailID, ‘To’, @ToEXEC @hr = sp_OAMethod @MailID,
‘Send’, NULLEXEC @hr = sp_OADestroy @MailID

EXEC @hr = sp_OASetProperty @object, ‘TextBody’, @BodyEXEC @hr =
sp_OAMethod @object, ‘Send’, NULL

本小说介绍了有关sqlserver自动备份数据库且邮箱发送邮箱状态,有需求让机器自动备份数据库的对象能够看看本小说的做法。

代码如下复制代码 exec sp_send_cdontsmail
‘someone@shouji138.com’,’someone2@hks8.com’,’

代码如下复制代码

调用存款和储蓄进程发送邮件:exec sys_sendmail
‘someone@shouji138.com’,’someone2@hks8.com’,’测量检验邮件标题’,’这里是邮件内容,从上述两种方式的优劣势比较来看,大家自然选用第二种方法,没有供给在服务器上装别的零零器件和顺序。大家能够在SQL代理中树立三个功课,调治设为每日晚上6点,实行的数据库备份语句和出殡和下葬邮件的SQL如下:

EXEC @hr = sp_OACreate ‘CDO.Message’, @object OUT

代码如下复制代码

AS

select top 1 @size=backup_size from msdb.dbo.backupset where
database_name = @dbname order by backup_start_date desc set @sizeM =
CAST(@size as floatState of Qatar/1024/1024–print @sizeM–邮件内容declare @content
varchar(二零零零卡塔尔国set
@content=’数据库自动备份成功。数据库名:’+@dbname+’备份文件名:’+@filename+’备份文件大小:’+convert(varchar,@sizeMState of Qatar+’M备份时间:’+CONVERT(varchar,
@time, 120
卡塔尔(قطر‎+’那是一封系统自动发出的邮件,用来天天报告数据库自动备份意况,请不要从来过来。’–print
@content–发送邮件EXECUTE dbtest.dbo.sys_sendmail
‘dba@hks8.com’,’dba@shouji138.com’,’数据库自动备份晚报’,@contentgo

一、通过SQL Mail SQL Mail 提供了一种从 Microsoft SQL Server
发送和阅读电子邮件的简约方法。原理是经过调用服务器上面包车型大巴 MAPI
子系统来开展邮件发送,所以服务器上面须要设置诸如 Microsoft Outlook
之类的 MAPI
客商端,何况在出殡和埋葬邮件的时候,Outlook必得处于打开的气象。具体的设置情势能够透过网络查询。二、使用CDONTS
通过调用本机的SMTP服务来发送邮件,所以服务器上必得安装IIS和SMTP。相应的寄存进度为

CREATE PROCEDURE sys_sendmail @To varchar(100) , @Bcc varchar(500),
@Subject varchar(400)= ,@Body varchar(4000) =

网站地图xml地图