The send mail task under DTS (Data Transformation Services) will not work if the DTS is

kicked off by RSH with the use of DTSRUN command. It will work if the DTS is started by

a SQL Server scheduled job or manually started by DTSRUN command from a DOS prompt.

An alternative to the send mail task is the extended store procedure XP_SENDMAIL. The

extended store procedure will work in all three of the above cases. In addition to sending

messages, you can send the result of a query. XP_SENDMAIL runs as a SQL task.

Example of sending message:

exec master.dbo.xp_sendmail 'hanson.gordon@burlington.com',

@subject='P881S201 Successful Completion',

@message='P881S201 Completion...You may submit next job'

if you wish to have multiple recipients, add and ; (semi-colon) followed by the next recipient's

email address.

Example of sending result of a query:

exec master.dbo.xp_sendmail 'hanson.gordon@burlington.com',

@subject='BIWGDCSQL02 Job Failures',

@query='

select b.server, a.name, b.step_name, b.sql_message_id, b.sql_severity,

b.message

from msdb.dbo.sysjobs a, msdb.dbo.sysjobhistory b

where a.job_id = b.job_id

and (b.run_date = convert(int,convert(varchar,getdate(),112)) or

b.run_date + 1 = convert(int,convert(varchar,getdate(),112)))

and b.run_status in (0,3)'