asked on

Send email with attachments from stored procedure in MS SQL 2005

Hi,

I want to create a sql job that will run every night at 3 AM and send emails.

I am able to send emails from the stored procedure using "msdb.dbo.sp_send_dbmail".

[[
SET @SQL = ' EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' + @Profile_Name + ''',
@recipients = ''' + @EmailRecipients + ''',
@body_format = ''html'',
@subject = ''' + @EmailSubjectToSend + ''',
@body = ''' + @EmailBodyToSend + ''';'

EXEC (@SQL)
]]

Now In many cases I want to attach some files (one or more than one) to the email. The attachments are stored in the database itself (in one table. The table structure is:

[[
CREATE TABLE [dbo].[tblCourseAutoEmailAttachments](
[AttachmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseAutomatedEmailID] [int] NULL,
[Attachment] [image] NULL,
[AttachmentName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AttachmentSize] [int] NULL)
]]

Please let me know how can I send emails with attachments from the stored procedure.

Thanks in advance.

Ankit

indianguru2

You have to use bcp to dump you Attachment to a temp folder on the Hard Drive and then use the database mail to pick that up and send.

This link will help you with the above task
http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

This can be used to send the mail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'me@somewhere.com',
@file_attachments = 'D:\File1.txt;D:\File2.txt'
@body = 'Test File Attachments',
@subject = 'Files Attached';

aplusexpert

ASKER

Thanks.

I will try this solution and let you know.

- Ankit

ASKER CERTIFIED SOLUTION

aplusexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial