One email for multiple items

Question

I have a table like this:

ServerName Email

Server1        [e-mail]

Server2        [e-mail]

server3        [e-mail]

Server4       [e-mail]

So basically one email address can have many servernames. What I am trying to do is send ONE email with all the related servernames.

---

Alan

Answer 1

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,'[e-mail]')

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.

Answer 2

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

Answer 3

Hi,

Try someting like this:

USE [tempdb];
GO

CREATE TABLE [Email]
(
	[ServerName] varchar(50),
	[EmailAddress] varchar(100)
);
GO

INSERT INTO [Email] VALUES
('Server1', '[e-mail]'),
('Server2', '[e-mail]'),
('Server3', '[e-mail]');
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