Return values from XP_SendMail in Stored Procedure

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)

Return values from XP_SendMail in Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-01 : 08:25:30

Mike writes "SQL 2000 SP2

I have a stored procedure that loops thru an address list and uses XP_Sendmail to fire off e-mails to my users.

How can I get this stored procedure to trap the return codes from XP_Sendmail so I can respond to errors, like bad e-mail addresses.

Here's a sample of the code I am currently using, that obviously is not handling my situation:

IF @totalcost >=100
BEGIN
select @querydata = 'SELECT userid AS UserID, calls AS [# Calls], callhours AS [Total Hours Connected], cast(totalcost as money) AS [Total Cost], hostname AS [Host Name] FROM uunet_data.dbo.rawdata WHERE [userid] = ''' + @userid + ''' AND [timeperiod] = ''' + cast(@timeperiod as varchar) + ''' and [status] = ''U'''

SELECT @subjectline = @subjectline1 + DATENAME(month,@timeperiod) + ' ' + CAST(DATEPART(yyyy,@timeperiod) AS VARCHAR)

EXEC master.dbo.xp_sendmail @recipients = @userid, @subject = @subjectline, @message = @sendmessage, @query = @querydata, @attach_results = false, @width = 120
SELECT @mailerror = @@Error

If @mailError <> 0
BEGIN
update rawdata set eMailStatus = 'E' WHERE [userid] = @userid AND [timeperiod] = cast(@timeperiod as varchar)
END
ELSE
BEGIN
update rawdata set eMailStatus = 'C' WHERE [userid] = @userid AND [timeperiod] = cast(@timeperiod as varchar)
END
END

If there is an error sending e-mail for a particular record I want to mark that record accordingly for later review.

I know from the text output of this procedure I am getting errors, I haven't figured out how to trap them properly yet.

Thanks,

Mike Hulen
Baker Petrolite, Inc.
Sugar Land, TX"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-01 : 08:44:01


DECLARE @ret int

IF @totalcost >=100 
BEGIN 
select @querydata = 'SELECT userid AS UserID, calls AS [# Calls], callhours AS [Total Hours Connected], cast(totalcost as money) AS [Total Cost], hostname AS [Host Name] FROM uunet_data.dbo.rawdata WHERE [userid] = ''' + @userid + ''' AND [timeperiod] = ''' + cast(@timeperiod as varchar) + ''' and [status] = ''U''' 

SELECT @subjectline = @subjectline1 + DATENAME(month,@timeperiod) + ' ' + CAST(DATEPART(yyyy,@timeperiod) AS VARCHAR) 

EXEC @ret= master.dbo.xp_sendmail @recipients = @userid, @subject = @subjectline, @message = @sendmessage, @query = @querydata, @attach_results = false, @width = 120 


If @ret != 0
BEGIN 
update rawdata set eMailStatus = 'E' WHERE [userid] = @userid AND [timeperiod] = cast(@timeperiod as varchar) 
END 
ELSE 
BEGIN 
update rawdata set eMailStatus = 'C' WHERE [userid] = @userid AND [timeperiod] = cast(@timeperiod as varchar) 
END 
END

HTH
Jasper Smith

Edited by - jasper_smith on 08/01/2002 08:45:30

MikeHulen
Starting Member

2 Posts

Posted - 2002-08-02 : 11:12:27

Thanks much.

I have no idea how I missed that solution.

In the words of Homer Simpson...DOH!

Mike Hulen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources