How to send email from SQL Server?

by

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="[email protected]",
@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<=@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>@id

END

Posted this on the following link http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. How do I escape a single quote in SQL Server?
  6. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  7. Selecting data from two different servers in SQL Server
  8. SQL JOIN vs IN performance?
  9. TSQL Pivot without aggregate function
  10. How to generate a range of dates in SQL Server
  11. SQL RANK() versus ROW_NUMBER()
  12. Generate SQL Create Scripts for existing tables with Query
  13. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  14. How do I delete from multiple tables using INNER JOIN in SQL server
  15. Check if a row exists, otherwise insert
  16. SQL : BETWEEN vs =
  17. Need to list all triggers in SQL Server database with table name and table’s schema
  18. Count based on condition in SQL Server
  19. Alter user defined type in SQL Server
  20. Best equivalent for IsInteger in SQL Server
  21. Store multiple bit values in a single table column
  22. Syntax of for-loop in SQL Server
  23. SQL Server equivalent to MySQL enum data type?
  24. How to correctly insert newline in nvarchar [duplicate]
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment