Help with xp..sendmail needed!

Hi,
I keep getting the following error in my SP which I can't seem to resolve:-
Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient
If anyone has any ideas why this is happening it would be greatly
appreciated. My SP is below for reference.
DECLARE @.REG_NO as varchar(10)
DECLARE @.URN as varchar(10)
DECLARE @.OFFICER as varchar(10)
DECLARE @.RENEWAL_DATE as varchar(10)
DECLARE @.OFFICER_EMAIL as varchar(15)
DECLARE @.sbj as varchar(50)
DECLARE @.msg as varchar(50)
DECLARE @.recipient as varchar(50)
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT
REG_NO = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = CONVERT(VARCHAR,d.sd_renewal_date, 103),
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
--SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.recipient = @.OFFICER_EMAIL
select @.sbj = 'List of Renewal Dates'
select @.msg = @.REG_NO + ', ' + @.URN + ', ' + @.OFFICER + ',' + @.RENEWAL_DATE
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
GO
Really appreciate any help on this.
Thanks
DamonDoes xp_sendmail work if you call it manually (e.g. not populating the
elements with variables)? If you can't get xp_sendmail to work at all, you
might save time by reading http://www.aspfaq.com/2403 and using an
alternative. If you are using SQL Server 2005, you should consider database
mail instead (sorry, the article hasn't been updated for that).
Did you try wrapping the call in
IF @.OFFICER_EMAIL IS NOT NULL
BEGIN
..
END
Or better yet, have an e-mail format checking function,
IF dbo.IsEmail(@.OFFICER_EMAIL)
BEGIN
..
END
xp_sendmail also seems to be fussy about trailing spaces, and while they
shouldn't be there because it is varchar, you could try:
SET @.OFFICER_EMAIL = LTRIM(RTRIM(@.OFFICER_EMAIL))
Finally, if @.OFFICER_EMAIL is a distribution list, see
http://support.microsoft.com/kb/315666
"Damon" <nonsense@.nononsense.com> wrote in message
news:2oQsf.18319$mn1.4396@.newsfe7-win.ntli.net...
> Hi,
> I keep getting the following error in my SP which I can't seem to
> resolve:-
> Server: Msg 18031, Level 16, State 1, Line 0
> xp_sendmail: Could not resolve recipient
> If anyone has any ideas why this is happening it would be greatly
> appreciated. My SP is below for reference.
> DECLARE @.REG_NO as varchar(10)
> DECLARE @.URN as varchar(10)
> DECLARE @.OFFICER as varchar(10)
> DECLARE @.RENEWAL_DATE as varchar(10)
> DECLARE @.OFFICER_EMAIL as varchar(15)
> DECLARE @.sbj as varchar(50)
> DECLARE @.msg as varchar(50)
> DECLARE @.recipient as varchar(50)
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT
> REG_NO = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = CONVERT(VARCHAR,d.sd_renewal_date, 103),
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> --SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.recipient = @.OFFICER_EMAIL
> select @.sbj = 'List of Renewal Dates'
> select @.msg = @.REG_NO + ', ' + @.URN + ', ' + @.OFFICER + ',' +
> @.RENEWAL_DATE
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
> GO
>
>
> Really appreciate any help on this.
> Thanks
> Damon
>|||hi,
Thank you very much for your response. I have tried xp_sendmail manually by
typing it into Query Analyzer and it works fine. It doesn't seem to like it
in my SP.
I have also added the IF @.OFFICER_EMAIL IS NOT NULL and SET @.OFFICER_EMAIL =
LTRIM(RTRIM(@.OFFICER_EMAIL)) into my Stored Procedure but I still get the
same error.
Any more advice is greatly appreciated.
Thanks
Damon
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e2vgJxHDGHA.528@.TK2MSFTNGP09.phx.gbl...
> Does xp_sendmail work if you call it manually (e.g. not populating the
> elements with variables)? If you can't get xp_sendmail to work at all,
> you might save time by reading http://www.aspfaq.com/2403 and using an
> alternative. If you are using SQL Server 2005, you should consider
> database mail instead (sorry, the article hasn't been updated for that).
> Did you try wrapping the call in
> IF @.OFFICER_EMAIL IS NOT NULL
> BEGIN
> ...
> END
> Or better yet, have an e-mail format checking function,
> IF dbo.IsEmail(@.OFFICER_EMAIL)
> BEGIN
> ...
> END
> xp_sendmail also seems to be fussy about trailing spaces, and while they
> shouldn't be there because it is varchar, you could try:
> SET @.OFFICER_EMAIL = LTRIM(RTRIM(@.OFFICER_EMAIL))
> Finally, if @.OFFICER_EMAIL is a distribution list, see
> http://support.microsoft.com/kb/315666
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:2oQsf.18319$mn1.4396@.newsfe7-win.ntli.net...
>|||> Any more advice is greatly appreciated.

> DECLARE @.OFFICER_EMAIL as varchar(15)
Well, is it remotely possible that any of your e-mail addresses are longer
than 15 characters? What is the table structure of tbl_email? What is
the datatype of e_officer_email?|||Do you know what; I think I just made a complete rookie mistake. It was the
DECLARE @.OFFICER_EMAIL as varchar(15). It should have been 30.
I cannot thank you enough.
Damon
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eFP6phIDGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
> Well, is it remotely possible that any of your e-mail addresses are longer
> than 15 characters? What is the table structure of tbl_email? What is
> the datatype of e_officer_email?
>|||> Do you know what; I think I just made a complete rookie mistake. It was
> the DECLARE @.OFFICER_EMAIL as varchar(15). It should have been 30.
Our systems use VARCHAR(128). Even I have e-mail addresses that are > 30
characters, and I don't have an abnormally long name or domain names...|||Good idea, I think I will chane it to that.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXyqv1IDGHA.208@.TK2MSFTNGP10.phx.gbl...
> Our systems use VARCHAR(128). Even I have e-mail addresses that are > 30
> characters, and I don't have an abnormally long name or domain names...
>