Flexi xp_sendmail

dileeparya used Ask the Experts™
May some one help me in fetching email address from a database say usermast table in users db on SQL2000 sql server .

Want to send mail to users ( username columns in usermast table ) as a job at specific time say 00:00 hrs based on database entry done on previous day.

Can this be made dynamic.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Questions: What part of this procedure do you know already?
1) querying a table
2) sending email
3) starting sql at specific time

Let's start by the "easy" things:
3) you should know that SQL Server Agent can have jobs + schedules to start any SQL at any time.

2) you know how to send emails, including having parameters to run the procedure xp_sendmail

1) This can be done either using cursors (slow performance), or a tsql loop code on the table.

Hi Dilip,
xp_sendmail can take a list of usernames to send an email to. This list should be in the format:
<UserName1>;<UserName2>;<UserName3>;.....etc etc

To get the list of users from your table into a variable ready to pass to xp_sendmail, you'd do something like:

DECLARE @pstrUserList VARCHAR(1000)
SET @pstrUserList = ''

-- Put in WHERE clause if required to restrict users returned to be emailed.
SELECT @pstrUserList = @pstrUserList + ';' + username
FROM users.dbo.usermast

-- Trim off the preceding ";"
SET @pstrUserList = RIGHT(@pstrUserList, LEN(@pstrUserList) - 1)

IF ( LEN(ISNULL(@pstrUserList, '')) > 0 )
      -- Basic example of sending email to users
      EXEC xp_sendmail @pstrUserList, '<EMAIL TEXT HERE>'

You can put this into a stored procedure and then create a DTS package to call this procedure. Once you have the DTS package, you can schedule it to run when you require.

Hope that helps
Just to correct myself, the EXEC statement should be:

EXEC master.dbo.xp_sendmail @pstrUserList, '<EMAIL TEXT HERE>'
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
No comment has been added lately (158 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to angelIII

Please leave any comments here within 4 days.

-- Please DO NOT accept this comment as an answer ! --


EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial