How to send email from SQL Server

Question

How can I send an email using T-SQL but email address is stored in a table  I want to loop through the table and be able to send email  I cannot find a good example of doing this so far

User · Accepted Answer

Step 1  Create Profile and Account  You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node  This wizard is used to manage accounts  profiles  and Database Mail global settings   Step 2    RUN   sp CONFIGURE  show advanced   1 GO RECONFIGURE GO sp CONFIGURE  Database Mail XPs   1 GO RECONFIGURE GO   Step 3   USE msdb GO EXEC sp send dbmail  profile name  yourprofilename    recipients  test Example com    subject  Test message    body  This is the body of the test message  Congrates Database Mail Received By you Successfully     To loop through the table   DECLARE  email id NVARCHAR 450    id BIGINT   max id BIGINT   query NVARCHAR 1000   SELECT  id MIN id    max id MAX id  FROM  email adresses   WHILE  id lt   max id BEGIN     SELECT  email id email id      FROM  email adresses       set  query  sp send dbmail  profile name   yourprofilename                             recipients      email id                               subject   Test message                             body   This is the body of the test message                          Congrates Database Mail Received By you Successfully          EXEC  query     SELECT  id MIN id  FROM  email adresses  where id gt  id  END   Posted this on the following link http   ms-sql-queries blogspot in 2012 12 how-to-send-email-from-sql-server html

User · Answer

You can send email natively from within SQL Server using Database Mail  This is a great tool for notifying sysadmins about errors or other database events  You could also use it to send a report or an email message to an end user  The basic syntax for this is   EXEC msdb dbo sp send dbmail    recipients  user yourdomain com    subject  Testing Email from SQL Server    body   lt p gt It Worked  lt  p gt  lt p gt Email sent successfully lt  p gt     body format  HTML    from address  Sender Name  lt sender yourdomain com gt     reply to  sender yourdomain com    Before use  Database Mail must be enabled using the Database Mail Configuration Wizard  or sp configure  A database or Exchange admin might need to help you configure this  See  http   msdn microsoft com en-us library ms190307 aspx  and  http   www codeproject com Articles 485124 Configuring-Database-Mail-in-SQL-Server for more information

User · Answer

In-order to make SQL server send email notification you need to create mail profile from Management  database mail   1  User Right click to get the mail profile menu and choose configure database mail   2 choose the first open  set up a database mail by following the following tasks  and press next Note  if the SMTP is not configured please refer the the URL below  http   www symantec com business support index page content amp id TECH86263  3  in the second screen fill the the profile name and add SMTP account  then press next  4  choose the type of mail account   public or private   then press next  5  change the parameters that related to the sending mail options  and press next 6  press finish  Now to make SQL server send an email if action X happened you can do that via trigger or job   This is the common ways not the only ones    1  you can create Job from SQL server agent  then right click on operators and check mails  fill the your email for example  and press OK after that right click  Jobs  and choose new job and fill the required info as well as the from steps  name     etc and from notification tab select the profile you made   2  from triggers please refer to the example below   AS declare  results varchar max  declare  subjectText varchar max  declare  databaseName VARCHAR 255  SET  subjectText    your subject  SET  results    your results  -- write the Trigger JOB EXEC msdb dbo sp send dbmail   profile name    SQLAlerts     recipients    xxxx xxxx com     body    results    subject    subjectText    exclude query output   1 --Suppress  Mail Queued  message GO

User · Answer

Here s an example of how you might concatenate email addresses from a table into a single  recipients parameter   CREATE TABLE  emailAddresses  email VARCHAR 25    INSERT  emailAddresses  email  VALUES   foo foobar com   INSERT  emailAddresses  email  VALUES   bar foobar com   INSERT  emailAddresses  email  VALUES   buzzlightyear foobar com    DECLARE  recipients VARCHAR MAX  SELECT  recipients   COALESCE  recipients              email  FROM  emailAddresses  SELECT  recipients  DROP TABLE  emailAddresses   The resulting  recipients will be   foo foobar com bar foobar com buzzlightyear foobar com

User · Answer

To send mail through SQL Server we need to set up DB mail profile we can either use T-SQl or SQL Database mail option in sql server to create profile  After below code is used to send mail through query or stored procedure   Use below link to create DB mail profile  http   www freshcodehub com Article 42 configure-database-mail-in-sql-server-database  http   www freshcodehub com Article 43 create-a-database-mail-configuration-using-t-sql-script   x000D   x000D  --Sending Test Mail x000D  EXEC msdb dbo sp send dbmail x000D   profile name    TestProfile    x000D   recipients    To Email Here    x000D   copy recipients   CC Email Here               --For CC Email if exists x000D   blind copy recipients   BCC Email Here        --For BCC Email if exists x000D   subject    Mail Subject Here    x000D   body    Mail Body Here   x000D   body format  HTML   x000D   importance   HIGH   x000D   file attachments  C  Test pdf                 --For Attachments if exists x000D   x000D   x000D

User · Answer

sometimes while not found sp send dbmail directly  You may use  msdb dbo sp send dbmail  to try  Work fine on Windows Server 2008 R2 and is tested

User · Answer

You can do it with a cursor also  Assuming that you have created an Account and a Profile e g   profile  and an Account and you have the table that holds the emails ready e g   EmailMessageTable  you can do the following   USE database name GO SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  CREATE PROCEDURE mass email AS declare  email nvarchar  50   declare  body nvarchar  255     declare test cur cursor for              SELECT email from  dbo   EmailMessageTable   open test cur                                          fetch next from test cur into     email      while   fetch status   0        begin                                      set  body    SELECT body from  dbo   EmailMessageTable  where email    email  EXEC msdb dbo sp send dbmail      profile name    profile        recipients    email       body    body       subject    Credentials for Web   fetch next from test cur into    email  end     close test cur    deallocate test cur   After that all you have to do is execute the Stored Procedure      EXECUTE mass email GO

[sql] How to send email from SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sp-send-dbmail