Send mail task on SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Send mail task on SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-09 : 09:27:21

Deepika writes "I have a number of tasks in my DTS package ( e.g: transform data task, bulk insert, Execute SQL etc).. On failure of any of these tasks, i need to send an email notification to the concerned users that a particular task has failed.. The number of users keep changing day-by-day i.e.new users( recipients of the SQL mail notification) are added everyday.. Currently, each time a new user gets added, i have to change the SQL mail task( which uses the stored procedure xp_sendmail).

Question:
How is it possible to add users without having to change the '@recipients' argument of xp_sendmail stored procedure, each time.

SQL Server version: SQL Server 2000
OS: Windows NT"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-09 : 10:02:32

Send the email to a distribution list which you can modify to your hearts content on your exchange server.

setBasedIsTheTruepath
<O>

Günnie
Starting Member

4 Posts

Posted - 2002-04-09 : 10:18:14

Hi,
I know it is not the best solution, but the most simple way is using a cursor scrolling through a Table which contains the recipients.

1st Create the Table:

CREATE TABLE MailRecips
( recip varchar(40) NOT NULL, topic varchar(40) NOT NULL )

recip is 1 recipient of a mail, topic is the key (or maybe the subject of the mail)

Entries in this table look like this...
recip - topic
-----------------------
john - Blabla
ronda - Blabla
tom - Another Topic
-----------------------

2nd scroll through your new table to fetch all recipients for xp_sendmail:

DECLARE @topic varchar(40)
SELECT @topic='Topic which should be sended'

DECLARE @recipOne varchar
DECLARE @recipAll varchar(1000)

--We declare and open the cursor for a specific mail
DECLARE crs CURSOR FOR
SELECT recip FROM MAILRecips WHERE topic = @topic

OPEN crs
FETCH NEXT FROM crs INTO @recipOne

--
--Scrolling through the table

SELECT @recipAll = ''
DO WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @recipAll = @recipAll + ';' + @recipOne
FETCH NEXT FROM crs INTO @recipOne
END
@recipAll=Right(@recipAll,Len(@recipAll)-1)

--
--Closing the cursor
CLOSE crs
DEALLOCATE crs

--
--Sending mail
EXEC xp_sendmail @recpients=@recipientAll, ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources