熟悉数据库邮件的朋友看过来。 怎么把查询赋给msdb.dbo.sp_send_dbmail里的参数 @recipients ?我的收件人都是在table里的,要select出来。或者怎么把存储过程sp_send_dbmail的外部参数传递到存储过程里面?谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 给你一个例子吧,我们正在用的1.email发送的配置表 id [serverHost] [serverPort] [useServerSSL] [useServerCredentials] [serverUserName] [serverPassWord] [fromEmail] 1 1.1.1.1 25 0 0 1 1 [email protected]2.建立需要发送email的数据表CREATE TABLE [dbo].[EmailList]( [emailID] [int] IDENTITY(1,1) NOT NULL, [campaignID] [int] NULL, [recipients] [text] COLLATE Chinese_PRC_CI_AS NULL, [emailBody] [text] COLLATE Chinese_PRC_CI_AS NULL, [emailSubject] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL, [mailConfigID] [int] NULL, [sendstate] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_EmailList] PRIMARY KEY CLUSTERED ( [emailID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]3.发送email封装的存储过程 set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[usp_sendmail]@To varchar(200) , @Subject varchar(400), @Body ntext,@EmailConfigID intASDeclare @object int,@hr int,@From varchar(100),@serverHost nvarchar(100),@userName nvarchar(100), @passWord nvarchar(50),@useSSL int,@ssl nvarchar(10),@useServerCredentials intselect @serverHost=serverHost,@From=fromEmail,@userName=serverUserName,@passWord=serverPassWord,@useSSL=useServerSSL,@useServerCredentials=useServerCredentials from dbo.EmailConfig where id=@EmailConfigIDif @useSSL=1begin set @ssl='true'endelsebegin set @ssl='false'endEXEC @hr = sp_OACreate 'CDO.Message', @object OUTEXEC @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', @serverHostEXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").Value', @sslif @useServerCredentials=1begin 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',@userName EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@passWordendEXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', nullEXEC @hr = sp_OASetProperty @object, 'To', @ToEXEC @hr = sp_OASetProperty @object, 'From', @FromEXEC @hr = sp_OASetProperty @object, 'Subject', @SubjectEXEC @hr = sp_OASetProperty @object, 'HTMLBody', @BodyEXEC @hr = sp_OAMethod @object, 'Send', NULL--判断出错IF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @object -- RETURN @objectENDEXEC @hr = sp_OADestroy @object4.从表中提取数据,进行发送 declare @to nvarchar(1000),@subject nvarchar(100),@body nvarchar(4000), @id int, @count int select @count=count(*) from EmailList where sendstate='Await' while @count>0 begin select top 1 @id=emailID,@to=recipients,@subject=emailSubject,@body=emailBody from EmailList where sendstate='Await' exec usp_sendmail @to,@subject,@body,1 update EmailList set sendstate='Sent' where emailID=@id select @count=count(emailID) from EmailList where sendstate='Await' end 查询语句的问题 vc++中怎么取数据库的字段名 请问这个sql怎么写,好像蛮难的 求存储过程!!!!在线等………… 国内大型超市数据库问题 怎样让远端客户访问MS SQL 2000服务器上的数据? 各位大老帮帮俺 问个比较困惑的sql问题 两数据库之间DTS数据导出 如何设计表的结构以及sql语句实现树的遍历 求助!SQL SERVER2005 安装出错 EXCEL 中调用存储过程问题?
1.email发送的配置表
id [serverHost] [serverPort] [useServerSSL] [useServerCredentials] [serverUserName] [serverPassWord] [fromEmail]
1 1.1.1.1 25 0 0 1 1 [email protected]
2.建立需要发送email的数据表
CREATE TABLE [dbo].[EmailList](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[campaignID] [int] NULL,
[recipients] [text] COLLATE Chinese_PRC_CI_AS NULL,
[emailBody] [text] COLLATE Chinese_PRC_CI_AS NULL,
[emailSubject] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[mailConfigID] [int] NULL,
[sendstate] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_EmailList] PRIMARY KEY CLUSTERED
(
[emailID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]3.发送email封装的存储过程 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[usp_sendmail]
@To varchar(200) ,
@Subject varchar(400),
@Body ntext,
@EmailConfigID int
ASDeclare @object int,@hr int,@From varchar(100),@serverHost nvarchar(100),@userName nvarchar(100), @passWord nvarchar(50),@useSSL int,@ssl nvarchar(10),@useServerCredentials intselect @serverHost=serverHost,@From=fromEmail,@userName=serverUserName,@passWord=serverPassWord,@useSSL=useServerSSL,@useServerCredentials=useServerCredentials from dbo.EmailConfig where id=@EmailConfigID
if @useSSL=1
begin
set @ssl='true'
end
else
begin
set @ssl='false'
end
EXEC @hr = sp_OACreate 'CDO.Message', @object OUTEXEC @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', @serverHost
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").Value', @ssl
if @useServerCredentials=1
begin
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',@userName
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@passWord
endEXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @object, 'To', @To
EXEC @hr = sp_OASetProperty @object, 'From', @From
EXEC @hr = sp_OASetProperty @object, 'Subject', @SubjectEXEC @hr = sp_OASetProperty @object, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @object, 'Send', NULL--判断出错
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
-- RETURN @objectEND
EXEC @hr = sp_OADestroy @object
4.从表中提取数据,进行发送 declare @to nvarchar(1000),@subject nvarchar(100),@body nvarchar(4000), @id int, @count int select @count=count(*) from EmailList where sendstate='Await' while @count>0
begin
select top 1 @id=emailID,@to=recipients,@subject=emailSubject,@body=emailBody from EmailList where sendstate='Await'
exec usp_sendmail @to,@subject,@body,1
update EmailList set sendstate='Sent' where emailID=@id
select @count=count(emailID) from EmailList where sendstate='Await'
end