How to send Email using MS Sql

 To send emails using Microsoft SQL Server, you can utilize the sp_send_dbmail stored procedure. This procedure allows you to send emails directly from SQL Server by leveraging Database Mail, a component of SQL Server that enables you to send email messages from the database engine.

Here's a basic example of how you can use sp_send_dbmail to send an email:

1. Configure Database Mail: Before you can send emails from SQL Server, you need to configure Database Mail. You can do this through SQL Server Management Studio (SSMS) by navigating to Management > Database Mail and following the setup wizard.

2. Enable Database Mail: Ensure that Database Mail is enabled by running the following SQL command:

   sql
   EXEC sp_configure 'show advanced options', 1;
   RECONFIGURE;
   EXEC sp_configure 'Database Mail XPs', 1;
   RECONFIGURE;
   

3. Send Email using sp_send_dbmail: Once Database Mail is configured and enabled, you can use the sp_send_dbmail stored procedure to send emails. Here's an example query:

   sql
   EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'YourMailProfile',  -- Specify the Database Mail profile to use
      @recipients = 'recipient@example.com',  -- Recipient email address
      @subject = 'Test Email',  -- Email subject
      @body = 'This is a test email sent from SQL Server.',  -- Email body
      @body_format = 'TEXT';  -- Body format (TEXT or HTML)
   

   Replace 'YourMailProfile' with the name of the Database Mail profile you configured earlier, and 'recipient@example.com' with the recipient's email address. You can also customize the subject, body, and format of the email as needed.

4. Execute the Query: Execute the query containing the sp_send_dbmail stored procedure to send the email.

Make sure that you have the necessary permissions to execute sp_send_dbmail and that the Database Mail profile you're using is correctly configured and has access to an SMTP server for sending emails. Additionally, ensure that your SQL Server instance has network access to the SMTP server.