Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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.
0
petersti
Asked:
petersti
  • 3
  • 3
1 Solution
 
timexistCommented:
there are serial steps involved in this email process.
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.



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


Declare	@errmsg varchar(500),  @report_path varchar(255), @return_status int, @cmd varchar(255), @outVar INT
Declare @sql varchar(4000) 
Declare @servername varchar(200) 

-- you must pass a @from value
If @From is null and @db_mail_profile is null		
  Begin
	select @errmsg = 'No From Value Specified'
	RAISERROR(@errmsg, 11, 2) WITH SETERROR
	return -101
  End

If @to is null
  Begin
	select @errmsg = 'No To Value Specified'
	RAISERROR(@errmsg, 11, 2) WITH SETERROR
	return -101
  End


If @smtp_server is null and @db_mail_profile is null
  Begin
	select @errmsg = 'Either an SMTP Server or a database mail profile must be specified'
	RAISERROR(@errmsg, 11, 2) WITH SETERROR
	return -101
  End

If @db_mail_profile is not null
  Begin


 	select @servername = @@servername
	select @sql = 'EXEC msdb.dbo.sp_send_dbmail '
	   + '@profile_name =  ' + '''' + @db_mail_profile + '''' + ','
	   + '@recipients   =  ' + '''' + @to + '''' +','
	   + '@subject	   =  ' + '''' + @subject + '''' + ','
	   + '@body		   =  ' + '''' + @body + '''' + ','
	   + '@file_attachments	=  ' + '''' + @attachment + ''''

	select @sql = 'sqlcmd -E -S ' + @servername + ' -q' + ' "' + @sql + '"'


	exec @return_status = master..xp_cmdShell @sql


	If @return_status > 0
	  Begin
		return -101
	  End


  End
Else
  Begin

	Declare @iMsg int
	Declare @hr int
	Declare @source varchar(255)
	Declare @description varchar(500)
	Declare @output varchar(1000)
	EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

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

	-- This is to configure the Server Name or IP address.
	EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp_server

	-- configure the smtp_port 
	If @smtp_port is not null
		EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', @smtp_port
		
	-- configure the smtp_authentication 
	If @smtp_user is not null
	  Begin
		EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
		EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @smtp_user
		EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @smtp_password
	  End



	-- Save the configurations to the message object.
	EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null


	-- Set the e-mail parameters.
	EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
	EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
	EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject



	-- Add an attachment to the message
	If @attachment is not null
		--EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @attachment		
	  Begin
		Select @outVar  = NULL
		EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @outVar OUT, @attachment
	  End

	--
	-- Send E-Mail using 'TextBody'.
	--
	EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
	EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

	--
	-- Error handling.
	--
	IF @hr <>0
	  Begin

		EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

		select @errmsg = 'SMTP Error:  Source: '+ IsNull(@source, 'null') + '  Description:  ' + IsNull(@description, 'null')
		RAISERROR(@errmsg, 11, 2) WITH SETERROR
		EXEC @hr = sp_OADestroy @iMsg
		return -101

	  End

	-- Clean up the objects created.
	EXEC @hr = sp_OADestroy @iMsg
  End

Open in new window

0
 
peterstiAuthor Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
timexistCommented:
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.




0
 
timexistCommented:
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'

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

Open in new window

0
 
peterstiAuthor Commented:
I am unable to send email.Because I am getting error mail server is not connecting.
0
 
peterstiAuthor Commented:
Part of my problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now