How to send text email using sql server 2005

• ionu

  Grasshopper

  Points: 19

  More actions

  August 23, 2011 at 11:25 am

  #390254

  Hello

  I am trying to send text email using sql server 2005. i have few stored procedures that need to run everyday at particular times and the results need to be mailed to a person (or) group. How to do this?

  Please help me out.

  Thanks

• Lowell

  SSC Guru

  Points: 323547

  More actions

  August 23, 2011 at 11:41 am

  #1372177

  you'll need to set up database mail, which is very simple to do, and you'll need a known SMTP server with a usnername and password, most likely.

  the actual command is like this:

  declare @body1 varchar(4000)

  set @body1 = 'This is a Simple Email Example generated on ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

  ' to demonstrate sending a basic notification'

  --this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:

  /*

  Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112

  No global profile is configured. Specify a profile name in the @profile_name parameter.

  */

  EXEC msdb.dbo.sp_send_dbmail

  --@profile_name=My Default DBMail',

  @recipients='lowell@mydomain.com',

  @subject = 'Simple Email Example',

  @body = @body1,

  @body_format = 'HTML'

  --@body_format = 'TEXT'

  and here's a screenshot example of a setup using GoogleMail for the setup:

  

  after you run the code, it will say "Mail queued" as a message.

  look in this view to see if it failed or not:

  --everything ever sent

  select * from [msdb].[dbo].[sysmail_allitems]

  --everything that failed.

  SELECT

  err.[description],

  fail.*

  FROM [msdb].[dbo].[sysmail_event_log] err

  inner join [msdb].[dbo].[sysmail_faileditems] fail

  ON err.mailitem_id = fail.mailitem_id

  hope that gets you started.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!