How to send email from SQL Server || Using Database Mail to Send Emails in SQL Server || Configuring Email Notifications in SQL Server

 To send an email in a SQL Server trigger using SMTP2GO, you’ll need to create a stored procedure that sends emails through SMTP and call this procedure from within your trigger. Here’s a step-by-step guide to setting this up.

1. Enable Database Mail in SQL Server

First, ensure Database Mail is enabled on your SQL Server instance. Database Mail is required to send emails from SQL Server.

1. In SQL Server Management Studio (SSMS), go to Management > Database Mail.
2. Right-click and select Configure Database Mail to set up a mail profile if you haven't done so already.

2. Configure SMTP2GO Settings

Set up SMTP2GO as the SMTP server in SQL Server Database Mail:

• SMTP Server: mail.smtp2go.com
• SMTP Port: 2525 (or 587/465 for secure connections)
• Authentication: Use your SMTP2GO username and password for authentication.

In the Database Mail configuration, add these SMTP server details.

3. Create a Stored Procedure to Send Email

Now, create a stored procedure to send emails through Database Mail. This stored procedure will be called from your trigger.

CREATE PROCEDURE SendEmailUsingSMTP2GO

    @recipient NVARCHAR(255),

    @subject NVARCHAR(255),

    @body NVARCHAR(MAX)

AS

BEGIN

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'SMTP2GOProfile', -- Use the profile name you configured

        @recipients = @recipient,

        @subject = @subject,

        @body = @body,

        @body_format = 'HTML';

END

Replace 'SMTP2GOProfile' with the actual profile name created for Database Mail.

4. Create the Trigger to Call the Stored Procedure

Now, create a trigger that calls the SendEmailUsingSMTP2GO stored procedure to send an email when a specific action occurs on the table.

CREATE TRIGGER trgSendEmailOnInsert

ON YourTableName

AFTER INSERT

AS

BEGIN

    DECLARE @recipient NVARCHAR(255);

    DECLARE @subject NVARCHAR(255);

    DECLARE @body NVARCHAR(MAX);

    -- Set the recipient, subject, and body of the email

    SET @recipient = 'recipient@example.com';

    SET @subject = 'New Record Inserted';

    SET @body = 'A new record has been inserted into the table.';

    -- Call the stored procedure to send the email

    EXEC SendEmailUsingSMTP2GO

        @recipient = @recipient,

        @subject = @subject,

        @body = @body;

END

Replace YourTableName with the name of your table, and customize the @recipient, @subject, and @body variables as needed.

5. Test the Trigger

Insert a row into YourTableName to test if the trigger works and if the email is sent via SMTP2GO.

sql

INSERT INTO YourTableName (Column1, Column2) VALUES ('Value1', 'Value2');

If everything is set up correctly, you should receive an email at the specified recipient address.

Important Notes:

• Avoid Heavy Email Load in Triggers: Triggers are synchronous with the transaction, so email sending will block the transaction until it completes. If you expect high frequency, consider logging events and using a separate job to send emails.
• Error Handling: Consider adding error handling in your stored procedure or trigger to handle email sending failures gracefully.