MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang · 发表于 2012-6-17 11:19

wuzd12 发表于 2012-6-13 10:48
学习中，得到进步，大家会感谢你无私的奉献，在路上，继续支持

哥，你2005年就注册啦。技术积分是不是少了点哦~

路人ABCD · 发表于 2012-6-20 16:08

本帖最后由路人ABCD 于 2012-6-20 16:08 编辑

分享一个存储过程，是建立一个发送Email的存储过程。

Create PROCEDURE [dbo].[Sp_SendEmail]
@From varchar(100)='EmailAddress@163.com' ,
@To varchar(100)='RecipientUser@163.com' ,
@Bcc varchar(500)='',
@Subject varchar(400)='测试',
@Body ntext ='测试邮件！'

AS
--配置先执行
/*
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO*/
if exists(select 1)
begin
Declare @object int
Declare @hr int

EXEC @hr = sp_OACreate 'CDO.Message', @object OUT

EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.163.com'

--下面三条语句是smtp验证，如果服务器需要验证，则必须要这三句，你需要修改用户名和密码
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','EmailAddress@163.com'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','EmailPassword'

EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @object, 'To', @To
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
EXEC @hr = sp_OASetProperty @object, 'From', @From
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @object, 'TextBody', @Body
EXEC @hr = sp_OAMethod @object, 'Send', NULL

--判断出错
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
print '发送失败'
RETURN @object
END
PRINT 'success'
EXEC @hr = sp_OADestroy @object
end

Cherish_j_wang · 发表于 2012-6-23 08:55

路人ABCD 发表于 2012-6-20 16:08
分享一个存储过程，是建立一个发送Email的存储过程。

Create PROCEDURE [dbo].[Sp_SendEmail]

哇哦~自己写一个邮件发送功能的SP啊，很强悍哦，佩服。不过SQL Server 20008，微软自己提供有系统的存储过程，用来发送邮件的。
msdb.dbo.sp_send_dbmail

hwtong · 发表于 2012-6-23 21:33

我这也有个发送邮件的存储过程.2000,2005,2008都可以.

CREATE PROCEDURE [dbo].[SendMail_cdo_sp]
@From varchar(100) ,
@To varchar(8000) ,
@Cc varchar(8000) = '',
@Bcc varchar(8000) = '',
@Subject varchar(200)=" ",
@Body varchar(8000) =" " ,
@Attachment varchar(8000) = '',
@SMTPServer varchar(20) ='', -- Added by Darrell 07/07/2006
@SMTPServer2 varchar(20) ='' -- Added by Darrell 07/07/2006
/*********************************************************************
To Send Mail:
exec master..SendMail_cdo_sp
@From = 'darrellshen@foxconn.com',
@To = 'darrellshen@q-edge.com;darrellshen@foxconn.com',
@Cc = '',
@Bcc = '',
@Subject = 'Subject',
@Body = 'Mail Body -- My Message',
@Attachment = ''
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @ErrorMessage varchar(200)
IF @SMTPServer = '' Set @SMTPServer = '10.17.48.12'
IF @SMTPServer2 = '' Set @SMTPServer2 = '10.17.49.15'
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
IF @hr <> 0
begin
set @ErrorMessage = 'Error On CreateObject'
GOTO ObjectError
end
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
send_cdosysmail_config:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Configuration'
GOTO ObjectError
end
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Configuration SMTP Server ' + @SMTPServer
GOTO ObjectError
end
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Configuration Update'
GOTO ObjectError
end
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Setting From Address'
GOTO ObjectError
end
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Setting To Address'
GOTO ObjectError
end
if len(@Cc) > 0
begin
EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @Cc
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Setting To Address'
GOTO ObjectError
end
end
if len(@Bcc) > 0
begin
EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @Bcc
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Setting To Address'
GOTO ObjectError
end
end
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Setting Subject'
GOTO ObjectError
end
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Setting Mail Body'
GOTO ObjectError
end
if len(@Attachment) > 0
begin
exec master..xp_fileexist @Attachment, @hr output
if @hr = 1
--EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
EXEC sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
else
begin
set @ErrorMessage = @Attachment + 'Does Not Exist !!'
GOTO ObjectError
end
end
print 'Using SMTP Server ' + @SMTPServer + ' On ' + convert(char(19),getdate(),120)
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0 and @SMTPServer <> @SMTPServer2
BEGIN
-- Support the 2nd SMTP Server 07/07/2006
set @SMTPServer = @SMTPServer2
GOTO send_cdosysmail_config
END
IF @hr <> 0
begin
set @ErrorMessage = 'Error On Send Mail'
GOTO ObjectError
end
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg
-- handle the failure of the destroy if needed
IF @hr <> 0 GOTO ObjectError
END
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
Print @ErrorMessage
RETURN 1
END
GO

复制代码

路人ABCD · 发表于 2012-6-23 23:41

请教一个问题，发送邮件的存储过程只能是少量纯文本的，如何定义才可以发送大量的文本信息呢？
另外，如果邮件需要带上附件该如何实现比较好呢？

hwtong · 发表于 2012-6-24 13:20

路人ABCD 发表于 2012-6-23 23:41
请教一个问题，发送邮件的存储过程只能是少量纯文本的，如何定义才可以发送大量的文本信息呢？
另外，如果 ...

你可以看下我上面的存储过程,
里面可以发送附件.

Cherish_j_wang · 发表于 2012-6-25 08:18

本帖最后由 Cherish_j_wang 于 2012-6-25 16:16 编辑

路人ABCD 发表于 2012-6-23 23:41
请教一个问题，发送邮件的存储过程只能是少量纯文本的，如何定义才可以发送大量的文本信息呢？
另外，如果 ...

哎，其实，我在写这个帖子的时候，我就猜到肯定会有朋友问如此的问题。所以，我在帖子中不厌其烦的强调“慎用Database Mail”，请不要把我昂贵的数据库系统当成OUTLOOK来用。MSSQL Server提供这个功能，只是为中小型企业提供了发送邮件的解决方法，但是对于大型数据库系统，还是那句话，请”慎用Database Mail“。

回答你的问题：
1.大量文本：存储过程msdb.dbo.sp_send_dbmail中的邮件内容参数@body是nvarchar(max)，可以支持到2GB的文本，所以，应该够你使用了。
2.关于附件：该存储过程支持查询结果集附件和某个路径下的附件（由参数@file_attachments指定绝对路径，如多个附件，请使用分号隔开）两种方式。详情，参见帮助文档。

solearn · 发表于 2012-6-25 13:40

mark

zsping · 发表于 2012-6-27 09:50

非常感謝...大有用處

路人ABCD · 发表于 2012-6-27 19:18

问一个问题：sql 数据库跨服务器备份如何设置？
在一个广域网内，内网ip。

[精华] MSSQL DBA 精华总结[处理对象名称中特殊字符]

浏览过的版块