How to create and send mail in SQL Server 2008?

Question

I want to send E-mail from SQL Server 2008. What are the basic steps for that? Can you explain me if someone used this facility.

Any help will appreciated.

this link will helpful as commented by marc_s (also I have accepted an answer below) http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

• M.S.Nayak: @idstam How to move the same Question from SO to other?
• gbn: There are many articles out there google.co.uk/search?q=sql+server+dbmail
• marc_s: See: blog.sqlauthority.com/2008/08/23/… - use the new, current "SQLMail" facilities - not old, deprecated solutions like "DatabaseMail" or even "Outlook activation"
• M.S.Nayak: @marc_s Thanks Very usefull blog post....
• gbn: @marc_s: DB Mail is new, SQL Mail is legacy...

Answer 1

In management studio configure the database mail option under "Management" (both the account and the profile) and then use the stored procedure sp_send_dbmail in the msdb database.

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Answer 2

1. In SSMS object Explorer, click Management
2. Right-click Database Mail
3. Click Configure Database Mail

enter image description here

4.Select Set up Database Mail by performing the following tasks

enter image description here

5.Enter a profile name and click Add

enter image description here

6.Enter the settings for your email account. I could not set the account for my company's email account - it just didn't work. But I had no trouble with setting the Gmail account I use and these are the parameters that you should use for the Server name and Port number

enter image description here

It is not possible to send attachments from SSMS, you have to use T-SQL for that:

EXEC msdb.dbo.sp_send_dbmail
@profile_name='carol Gmail',
@recipients='[email protected]',
@subject='Test email',
@body='Attachment test',
@file_attachments='D:123円\pic.jpg'

Answer 3

Try the following SP.

CREATE procedure [dbo].[usp_send_cdosysmail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(max) ,
@smtpserver varchar(25),
@bodytype varchar(10) 
as
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'
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver 
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject
-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null
-- sample error handling.
if @hr <>0 
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg

• M.S.Nayak: Thanks for response. Can I include File attachment option? If How?
• marc_s: Way too complicated, way too risky (using the "sp_OA" stuff) and totally not necessary to do it this complicated..... just use the built-in SQL Server "SQLMail" - see how here: blog.sqlauthority.com/2008/08/23/…
• gbn: -1 this is so dated and obsolete. If you can use CDO you can use DatabaseMail because it is SMTP based