petersti
asked on
sending email from Sql server 2005 stored procedure
I want to send email alert while running the sql server stored procedure in 2005.
This sp generates the exception table .I want to include that table in that email.
This sp generates the exception table .I want to include that table in that email.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi Wizilling:,
I am getting the following error when I use the msdb.dbo.sp_send_dbmail
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
I am getting the following error when I use the msdb.dbo.sp_send_dbmail
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
create PROCEDURE [dbo].[AP_SEND_SMTP_MAIL]
@From varchar(255) = null,
@To varchar(8000) = null,
@Subject varchar(100)= ' ',
@Body varchar(4000) = ' ',
@Attachment varchar(5000)= null,
@SMTP_server varchar(16) = null,
@SMTP_port varchar(16) = '25',
@SMTP_user varchar(32) = null,
@SMTP_password varchar(64) = null,
@db_mail_profile sysname = null
WITH EXECUTE AS 'dbo'
AS
"WITH EXECUTE AS 'dbo'" is the key.
@From varchar(255) = null,
@To varchar(8000) = null,
@Subject varchar(100)= ' ',
@Body varchar(4000) = ' ',
@Attachment varchar(5000)= null,
@SMTP_server varchar(16) = null,
@SMTP_port varchar(16) = '25',
@SMTP_user varchar(32) = null,
@SMTP_password varchar(64) = null,
@db_mail_profile sysname = null
WITH EXECUTE AS 'dbo'
AS
"WITH EXECUTE AS 'dbo'" is the key.
You can enable the Database Mail feature by pressing the "Yes" button or by running the below sql sp_configure mail script
run this script to enable your 'Database Mail XPs'
run this script to enable your 'Database Mail XPs'
USE Master
GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure
GO
sp_configure 'show advanced options', 0
GO
ASKER
I am unable to send email.Because I am getting error mail server is not connecting.
ASKER
Part of my problem
step 1, setup database email profile.
step 2, enable database email.
step 3, save your exception in a table.
step 4, send out email include certain data fields and records of exception table.
so which step you are not very clear about?
for step 1, step 2
check this:
http://www.kodyaz.com/articles/sql2005-database-mail.aspx
and some code for sending the email.
to attach the table you use a loop to build the body.
Open in new window