locked
One email for multiple items RRS feed

Answers

  • Hi,

    Try someting like this:

    USE [tempdb];
    GO
    
    CREATE TABLE [Email]
    (
    	[ServerName] varchar(50),
    	[EmailAddress] varchar(100)
    );
    GO
    
    INSERT INTO [Email] VALUES
    ('Server1', 'joe@company.com'),
    ('Server2', 'joe@company.com'),
    ('Server3', 'jake@company.com');
    GO
    
    SELECT
    	[EmailAddress],
    (
    SELECT
    	[ServerName]
    FROM
    	[Email]
    WHERE
    	OQ.[EmailAddress] = [EmailAddress]
    FOR XML AUTO, ELEMENTS, TYPE)
    FROM
    	[Email] OQ
    GROUP BY
    	[EmailAddress];
    

    You have to have Database Mail configured and use the sp_send_dbmail sproc to send your emails.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    • Proposed as answer by Naomi N Sunday, January 22, 2012 3:15 AM
    • Marked as answer by Kalman Toth Thursday, January 26, 2012 11:47 AM
    Saturday, January 21, 2012 12:52 PM

All replies

  • I don't know I understood your question or not, just in case you are looking for the design of your table, and please have a look, and if you are looking for the email configuration via sql server then refer  http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

     

    CREATE TABLE   EMAIL(
    ID INT PRIMARY KEY,
    EMAIL VARCHAR(50))
    
    CREATE TABLE SERVERDETAILS(
    SERVERID INT PRIMARY KEY,
    EMAILID INT REFERENCES EMAIL(ID),
    SERVERNAME VARCHAR(10))
    
    INSERT INTO EMAIL VALUES(1,'JOE@COMPANY.COM')
    
    INSERT INTO SERVERDETAILS VALUES(1,1,'SERVER1'),(2,1,'SERVER2'),(3,1,'SERVER3'),(4,1,'SERVER4')
    
    SELECT * FROM EMAIL
    SELECT * FROM SERVERDETAILS
    



    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Saturday, January 21, 2012 12:30 PM
  • I already have a table with two fields. email and servername. I want to be able to send one email that contains all related servernames. Right now if the email has 6 related server names it's going to send 6 emails with only one servername in each.
    Alan
    Saturday, January 21, 2012 12:34 PM
  • Hi,

    Try someting like this:

    USE [tempdb];
    GO
    
    CREATE TABLE [Email]
    (
    	[ServerName] varchar(50),
    	[EmailAddress] varchar(100)
    );
    GO
    
    INSERT INTO [Email] VALUES
    ('Server1', 'joe@company.com'),
    ('Server2', 'joe@company.com'),
    ('Server3', 'jake@company.com');
    GO
    
    SELECT
    	[EmailAddress],
    (
    SELECT
    	[ServerName]
    FROM
    	[Email]
    WHERE
    	OQ.[EmailAddress] = [EmailAddress]
    FOR XML AUTO, ELEMENTS, TYPE)
    FROM
    	[Email] OQ
    GROUP BY
    	[EmailAddress];
    

    You have to have Database Mail configured and use the sp_send_dbmail sproc to send your emails.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    • Proposed as answer by Naomi N Sunday, January 22, 2012 3:15 AM
    • Marked as answer by Kalman Toth Thursday, January 26, 2012 11:47 AM
    Saturday, January 21, 2012 12:52 PM