master.dbo.xp_sendmail variable for recepient???

Question

Post reply

master.dbo.xp_sendmail variable for recepient???

Jeff-132889

Ten Centuries

Points: 1112
More actions
November 28, 2005 at 3:20 pm

#88582

Hi,

I have the following query. Works great with a hardcoded recepient. Gives error 'can't resolve recipient' when I try to make it populate from a query......... code is cut up a bit but you can see what I'm trying to do....

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
use master
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS',
'NORTHWIND', 'TEMPDB')
while @IDENT is not null
begin

SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT

SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @cmd varchar(8000)
declare @mailto varchar(128)
SET @cmd =      'SELECT + char(10) + firstname + '' '' + lastname + char(10) + email as staff
  from data.dbo.vwcurrentstaff where id = ''' +substring(@DBNAME,10,3)+ '-000'''

--does not work, if run in QA the result is mymail, what I hardcode and it works
SELECT @mailto = Email from mawdata.dbo.vwCurrentEDemails where id = 5'
--works just fine
SET @mailto = 'mymail'
EXEC master.dbo.xp_sendmail @mailto,
   @query = @cmd, @no_header= 'TRUE',
   @subject = @DBNAME,
   @width = 400


--increment counter code
end

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

Add:

PRINT '|'+@mailto+'|'

Watch out for spaces, invalid email addresses, and orphaned ; characters

and

PRINT '|'+@cmd+'|'

To double check the values while debugging, looks like you are missing a 4th ' at the end of your @cmd assignment, you can also pre-test the dynamic SQL query by adding:

EXEC (@cmd)

Add:

DECLARE @err int

EXEC @err = master.dbo.xp_sendmail @recipients = @mailto, ...

For debugging add

SELECT @err -- 0 = success, 1 = failure

I always use ISNULL(firstname,'')+... when concatenating otherwise any one column IS NULL = NULL as the variable value.

Break your script down to find where the issue is, or unit test the routine.

Andy

Richard Gardener SSC Veteran Points: 229 More actions · Answer 2

Hi,

You could try

SELECT @mailto = (SELECT Email from mawdata.dbo.vwCurrebentEDemails where id = '5')

instead of

SELECT @mailto = Email from mawdata.dbo.vwCurrentEDemails where id = 5'

Kindest regards

Richard

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 3

Thanks Guys. Andy, doesn't my SET CONCAT_NULL_YIELDS_NULL OFF do the same as you recommentd for null first names? Is it better not to use a global setting like that?

Thanks, Jeff