December 18, 2003 at 3:33 pm
I need to do two things - 1 is dynamically create a parm list to use with xp_sendmail. 2 is to capture the return code of xp_sendmail.
In order to dynamically add the parameters to the xp_sendmail call, I think I need to do something like the following:
set @exec = 'exec master..xp_sendmail '<plus> @parms
exec(@execution)
*@parms contains the comma seperated list of parameters
however, I can't capture the return code using exec(@execution). If I try:
set @exec = 'exec @rc = master..xp_sendmail '<plus> @parms
exec(@execution)
It tells me @rc has not been declared.
Does anyone know how I can capture the return code this way?
December 18, 2003 at 4:30 pm
The code you've got there gives that error because as far as exec(@exec) is concerned, @rc isn't declared. exec() is just a shell, I believe.
If using the shell method isn't required, you could call xp_sendmail directly with each parm separated by a comma.
I've got some code in a sproc that goes something like this:
DECLARE@SendTonvarchar(255),
DECLARE@SendTextnvarchar(255),
DECLARE@QueryTextvarchar(8000) = NULL
...SET values here...
IF @SendTo IS NOT NULL
BEGIN
IF @QueryText IS NULL
EXEC master.dbo.xp_sendmail @recipients = @SendTo, @message = @SendText,
@subject = 'This is a TEST of the new Procedure. It''s only a TEST.'
ELSE
EXEC master.dbo.xp_sendmail @recipients = @SendTo, @message = @SendText,
@subject = 'This is a TEST of the new Procedure. It''s only a TEST.',
@query = @QueryText,
@no_header = 'TRUE'
END
December 18, 2003 at 4:39 pm
In regards to your second requirement, xp_senmail will return only 0 or 1. You'll note my code ignores the return value. The code to get it is kinda backward, I think.
DECLARE @SendResult int
EXEC @SendResult = master.dbo.xp_sendmail @recipients = 'somebody@somewhere.org',@subject= 'Test'
PRINT @SendResult
HTH,
SJTerrill
December 18, 2003 at 4:43 pm
Ah, yes. Hadn't considered that. Good idea!
SJTerrill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply