September 30, 2009 at 3:38 pm
Ok here is some test data....
---===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#ChangeHistory','U') IS NOT NULL
DROP TABLE #ChangeHistory
--===== Create the test table with
CREATE TABLE [dbo].[#ChangeHistory](
[ChangeID] [int] IDENTITY(1,1) NOT NULL,
[ADUserName] [varchar](25) NULL,
[ChangeTypeDesc] [varchar](25) NULL,
[NewMailStreetLine1] [varchar](50) NULL,
[NewMailStreetLine2] [varchar](50) NULL,
[NewMailCity] [varchar](20) NULL,
[NewMailState] [varchar](3) NULL,
[NewMailZip] [varchar](11) NULL,
[EmailAddress] [varchar](90) NULL,
[DateChangeDetected] [datetime] NULL,
[EmailSent] [varchar](1) NULL,
CONSTRAINT [PK_ChangeHistory] PRIMARY KEY CLUSTERED
(
[ChangeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO #ChangeHistory
(
ADUserName,
ChangeTypeDesc ,
NewMailStreetLine1 ,
NewMailStreetLine2 ,
NewMailCity ,
NewMailState ,
NewMailZip,
EmailAddress,
DateChangeDetected ,
EmailSent
)
SELECT 'test1','Vendor','1234 test st','','LAS CRUCES','NM','88012','test1@abc.com','9/30/2009', 'N' UNION ALL
SELECT 'test1','Employee','12345 test ave','','LAS CRUCES','NM','88012','test1@abc.com','9/30/2009', 'N' UNION ALL
SELECT 'test2','Student','999 john doe ave','','El Paso','TX','79903','test2@abc.com','9/30/2009', 'N'
What I want to do is send out an email to each person in this table with the address on file. I know how to do this if it was one record per user, but some have more than 1 record like the 'test1' user. So for anyone that has more than record for todays date, I want to send them only ONE email but with both addresses. So for example for the 'test1' user he should get one email and it would look like this:
Hi test1,
Below are the address(es) we have listed for you on file.
Vendor
1234 test ST
LAS CRUCES, NM 88012
Employee
12345 test ave
LAS CRUCES, NM 88012
Please do not respond to this email, it's not monitored.
Test2 user would also receive an email, but it will only have 1 address listed in that email. I do do some updating after the email is sent (update emailsent field) so they wont receive future emails. I hope this is clear, any help would be greatly appreciated.
October 1, 2009 at 7:47 am
I guess I'm unclear on the question.
I'm assuming you're doing this with a cursor/loop and xp_sendmail?
Seems pretty straightforward if you already know how to process the table and send all the emails.
Here's the gist (Not full code).
DECLARE @CRLF char(2)
SET @CRLF = char(13) + char(10)
DECLARE ugh CURSOR LOCAL STATIC READ_ONLY FOR
SELECT DISTINCT ADUserName from #ChangeHistory
FETCH NEXT FROM ugh INTO @User
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Body = 'Header info for ' + @user + ', ' + @CRLF + @CRLF
SELECT @Body = @Body+ ISNULL(ChangeTypeDesc + @CRLF,@CRLF) +
--All other fields
FROM ChangeHistory
WHERE ADUserName = @User
SET @Body = @Body + 'Footer info'
exec xp_sendmail -- options
FETCH NEXT FROM ugh INTO @User
END
CLOSE ugh
DEALLOCATE ugh
Maybe I'm misreading a requirement?
October 1, 2009 at 8:15 am
I think you've got it... becareful with sendmail though, you want to test that before you push it to production, people get real angry when 500 emails come through from the same sender. 🙂
October 1, 2009 at 8:32 am
Thanks Seth, I had actually figured it out last night, but I didnt have access to internet to post. thanks for your input. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply