Sql-server – Configure sp_send_dbmail to send emails ONLY to certain recipients

sql server

For our environment test we need to configure sp_send_dbmail to send emails only to certain recipients. Let's say a stored procedure executes the following code:

EXEC msdb.dbo.sp_send_dbmail
    @recipients='developer@myorganization.com'; 'realuser@myorganization.com',
    @body = @myBody,
    @subject = @mySubject,
    @profile_name =@myProfileName

I need the email to be sent only to developer@myorganization.com, and not to realuser@myorganization.com. Is there a way that i can configure this?
Changing realusers email address won't do it for me.

Best Answer

There is nothing built in that you could use already.

I would suggest creating a "wrapper" stored procedure for sp_send_dbmail, that accepts the exact same parameters and passes them through, with additional logic in place to check the @ recipients parameter that is passed against your exclusion list.

Related Solutions

Sql-server – sp_send_dbmail stored procedure send with attachment

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server.

Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is:

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

You would then attach the file to the email in sp_send_dbmail.

USE msdb;
GO

EXEC sp_send_dbmail 
  @recipients='mark@mark.com',
  @subject='Client Report',
  @body='Please find your latest report attached',
  @file_attachments='D:\MyTable.csv';

You could, if you want, attach multiple files to one email.

Sql-server – Send Email To Multiple Recipients

You can break down the vendors using a CTE, prior to using the COALESCE function to get a list of emails, with one per vendor.

CREATE TABLE EmailMe
    (
      ID INT IDENTITY(1, 1)
             PRIMARY KEY
             NOT NULL ,
      name VARCHAR(100) ,
      vendorid INT ,
      email VARCHAR(500)
    );

INSERT  INTO EmailMe
        ( name, vendorid, email )
VALUES  ( 'purple', 12, 'purple@hsbcglobal.com' )
,       ( 'green', 12, 'green@hsbcglobal.com' )
,       ( 'blue', 1, 'blue@hsbcglobal.com' );

DECLARE @EmailRecipients VARCHAR(MAX);

;
WITH    cteIds
          AS ( SELECT   MAX(ID) AS Id
               FROM     EmailMe
               GROUP BY vendorid
             )
    SELECT  @EmailRecipients = COALESCE(@EmailRecipients + ';', '') + email
    FROM    EmailMe e
            JOIN cteIds c ON c.Id = e.ID;

SELECT  @EmailRecipients;

Results are:

blue@hsbcglobal.com;green@hsbcglobal.com

Best Answer

Related Solutions

Sql-server – sp_send_dbmail stored procedure send with attachment

Sql-server – Send Email To Multiple Recipients

Related Question