• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1081
  • Last Modified:

Send email with attachment from SQL Server 2005

Can someone help me to modify the attach sp-send-cdosysmail procedure to include an attachment to the email? The procedure work with just sending email with hardcoded body, but I want to be able to attach a file to the email or attach a set of records which result from a select sql statement.

I use the following procedure to call the sp-send-cdosysmail:

ALTER Procedure [TRP].[sp_Q_Send_Mail]
as
begin
DECLARE @Subject varchar(100),
 	@Body2 varchar(4000),
  	@JobName varchar(50),
 	@ServerName varchar(50),
	@output varchar(5000)

 SET @JobName = 'FFS'
 SELECT @ServerName = @@ServerName
 SET @Subject = 'The validation ' + @JobName + ' on ' + @ServerName + '  has failed. '
 SET @Body2 = @Subject + 'This is a test email sent from PSF_Gen Server '
 SET @output = 'Select * from TRP.Validation_check;'

 EXEC master..sp_send_cdosysmail
  	'First.Last@Domain.com',
 	'First.Last@Domain.com; First.Last@OtherDomain.com',
 	@Subject,
 	@Body2
end

Open in new window

sp-send-cdosysmail.doc
0
HNA071252
Asked:
HNA071252
  • 3
  • 2
4 Solutions
 
Jim P.Commented:
The sp_send_cdosysmail was a bolt on that MS came up with after they realized many companies didn't have Exchange or other MAPI compliant servers. It does not support attachments.

I suggest you look at using sp_send_dbmail instead. That is a built in and I've used it many times and in many ways to notify me and other users of stuff coming out of the SQL Servers.
0
 
HNA071252Author Commented:
Can you send me the codes and how to use/call it?
0
 
25112Commented:
Below is the syntax.. but before you run it, you should create the profile.. following the following link to know how to create the profile:

http://www.kodyaz.com/articles/sql2005-database-mail.aspx
---------------------------------------------
ALTER Procedure [TRP].[sp_Q_Send_Mail]
as
begin
DECLARE @eMAILSubject varchar(100),
       @Body2 varchar(4000),
        @JobName varchar(50),
       @ServerName varchar(50),
      @output varchar(5000)

 SET @JobName = 'FFS'
 SELECT @ServerName = @@ServerName
 SET @eMAILSubject = 'The validation ' + @JobName + ' on ' + @ServerName + '  has failed. '
 SET @Body2 = @Subject + 'This is a test email sent from PSF_Gen Server '
 SET @output = 'Select * from TRP.Validation_check;'

EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'PSF_Gen', /* Create this seperately */
				@recipients =   	'First.Last@Domain.com; First.Last@OtherDomain.com',
	   			@copy_recipients = 'First.Last@Domain.com',
				@subject = @eMAILSubject,
				@body = @Body2,
				@execute_query_database='PSF_Gen_database',
				@query_attachment_filename = 'Latest Attachments',
				@query = 'Select * from TRP.Validation_check'
				@body_format = 'HTML' ;	

end

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim P.Commented:
The  sp_send_dbmail is a built in SP.

This is the basic link for it: http://msdn.microsoft.com/en-us/library/ms190307.aspx

These are some good explanations on how to configure your mail and use it:
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
http://www.sqlservercentral.com/Forums/Topic821810-149-1.aspx
http://www.kodyaz.com/articles/sql-server-database-mail-sp_send_dbmail-example.aspx

One of these days I'll have to write an article on it.
0
 
25112Commented:
jimpen, in the code, we are putting the sp_send_dbmail into a stored proc. do you suggest avoiding the SP?
0
 
Jim P.Commented:
The sp_send_dbmail is fine in code. I've used it that way to see if I want to send files or data, etc.  The code 25112 provided is a good example.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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