March 13, 2003 at 9:13 am
I created a temp table of users for which I would like to send an email. The the users in this table changes each time the job is run (which is automated). So I would like to send an email to all the users in the table, dynamically. Does anybody have any suggestions?
March 13, 2003 at 9:27 am
If in the temp table you also have the email adreess, you could create a cursor with the email adreess, and execute the xp_cmdshell command.
For example:
DECLARE @cmd AS VARCHAR(1000)
DECLARE @email AS VARCHAR(50)
DECLARE curEmail CURSOR LOCAL FOR
SELECT email from Temp_Table
FETCH NEXT FROM curEmail INTO @email
OPEN curEmail
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @cmd = 'exec master.dbo.xp_sendmail '+''''+@email+''''+','+''''+'your message'+''''
FETCH NEXT FROM curEmail INTO @email
END
CLOSE curEmail
DEALLOCARE curEmail
March 13, 2003 at 9:40 am
That's the simplest method (above) with a cursor.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 13, 2003 at 1:08 pm
As suggested I used the code suggested below. After I execute it says it completed sucessfully. But I do not receive an email. I know xpsendmail works b/c I did it outside of this job. thanks
DECLARE @cmd AS VARCHAR(1000)
DECLARE @email AS VARCHAR(50)
DECLARE curEmail CURSOR local FOR
SELECT uid from #temptableproject
OPEN curEmail
FETCH NEXT FROM curEmail INTO @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @cmd = 'exec master.dbo.xp_sendmail '+''''+@email+''''+','+''''+'your message'+''''
FETCH NEXT FROM curEmail INTO @email
END
CLOSE curEmail
DEALLOCAtE curEmail
March 14, 2003 at 7:32 am
I printed the value of the variable and it is correct.
Also why does it have to run in cmdshell and not just as an ep_sendmail statement?
March 14, 2003 at 9:39 am
That was my mistake, I wanted to say xp_sendmail, not xp_cmdshell. And I also forgot to execute the xp_sendmail. So execute this:
DECLARE @cmd AS VARCHAR(1000)
DECLARE @email AS VARCHAR(50)
DECLARE curEmail CURSOR LOCAL FOR
SELECT email from Temp_Table
FETCH NEXT FROM curEmail INTO @email
OPEN curEmail
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @cmd = 'exec master.dbo.xp_sendmail '+''''+@email+''''+','+''''+'your message'+''''
EXEC (@cmd)--this is wat I forgot, sorry.
FETCH NEXT FROM curEmail INTO @email
END
CLOSE curEmail
DEALLOCARE curEmail
March 17, 2003 at 6:12 am
Be careful when using xp_sendMail...
Soon it wont be supported (and it already does not work on one half of a cluster)
See
http://support.microsoft.com/view/tn.asp?kb=312839
for its replacement.
It uses SMTP instead, and again can be made to run like the example below.
You can also add another line in the Microsoft code to deal with attachments (if required)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply