我写了一个存储过程用于在VB前台发送数据库邮件。但是我发现有一个问题就是,我在后台的时候发送邮件是没问题的,也就是说下面这些代码是可以成功执行的,而且邮件也能正常发送。但是我用VB前台调用这个存储过程发送邮件时就会发生错误,提示邮件配置文件无效。
CREATE PROCEDURE sp_SendingAlert
-- Add the parameters for the stored procedure here
@MailAddress varchar(255),
@AlertSubject varchar(50),
@AlertText varchar(100)
AS
BEGIN SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='EpullSqlMail',
@recipients = @MailAddress,
@subject = @AlertSubject,
@body = @AlertText
END
GOexec sp_SendingAlert '[email protected]','aa','asdlkfjaslkdfjasdf'VB前台程序如下:Public Sub sp_SendingAlert(MailAddress As String, MailSubject As String, MailText As String)
On Error GoTo errmsg
Dim adoComm As Object
'// 创建一个对象,我们用来调用存储过程
Set adoComm = CreateObject("ADODB.Command")
With adoComm
'// 设置连接,假设 adoConn 为已经连接的 ADODB.Connection 对象
.ActiveConnection = ConAcc
'// 类型为存储过程,adCmdStoredProc = 4
.CommandType = 4
'// 存储过程名称
.CommandText = "sp_SendingAlert"
.Parameters.Item("@MailAddress").Value = MailAddress
.Parameters.Item("@AlertSubject").Value = MailSubject
.Parameters.Item("@AlertText").Value = MailText
'// 执行存储过程
.Execute
End With
'// 释放对象
Set adoComm = Nothing
StbInfo ("Alert 已发出!")
Exit Sub
errmsg:
MsgBox Err.Description & ",执行存储过程-sp_SendingAlert 时出错。", vbCritical
End Sub
但是我在前台程序中调用这个VB过程时就会报错。代码如下:
sp_SendingAlert "[email protected]", "多领原因", AlertMsg
报错信息如下:
profile name is not valid麻烦大家帮忙分析一下是什么原因,谢谢!
CREATE PROCEDURE sp_SendingAlert
-- Add the parameters for the stored procedure here
@MailAddress varchar(255),
@AlertSubject varchar(50),
@AlertText varchar(100)
AS
BEGIN SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='EpullSqlMail',
@recipients = @MailAddress,
@subject = @AlertSubject,
@body = @AlertText
END
GOexec sp_SendingAlert '[email protected]','aa','asdlkfjaslkdfjasdf'VB前台程序如下:Public Sub sp_SendingAlert(MailAddress As String, MailSubject As String, MailText As String)
On Error GoTo errmsg
Dim adoComm As Object
'// 创建一个对象,我们用来调用存储过程
Set adoComm = CreateObject("ADODB.Command")
With adoComm
'// 设置连接,假设 adoConn 为已经连接的 ADODB.Connection 对象
.ActiveConnection = ConAcc
'// 类型为存储过程,adCmdStoredProc = 4
.CommandType = 4
'// 存储过程名称
.CommandText = "sp_SendingAlert"
.Parameters.Item("@MailAddress").Value = MailAddress
.Parameters.Item("@AlertSubject").Value = MailSubject
.Parameters.Item("@AlertText").Value = MailText
'// 执行存储过程
.Execute
End With
'// 释放对象
Set adoComm = Nothing
StbInfo ("Alert 已发出!")
Exit Sub
errmsg:
MsgBox Err.Description & ",执行存储过程-sp_SendingAlert 时出错。", vbCritical
End Sub
但是我在前台程序中调用这个VB过程时就会报错。代码如下:
sp_SendingAlert "[email protected]", "多领原因", AlertMsg
报错信息如下:
profile name is not valid麻烦大家帮忙分析一下是什么原因,谢谢!
USE msdb
GOEXEC dbo.sp_addrolemember @rolename = 'Databasemailuserrole',
@membername = 'epull'
goexec sysmail_add_principalprofile_sp @principal_name = 'epull'
,@profile_name = 'EpullSqlMail'
,@is_default = 1