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

can you do the following 1) make office_email varchar(100)  - I'm wondering
if it's getting truncated,
2) can you put a print statement and then run it manually? i.e.

select @recipient = @OFFICER_EMAIL
select @sbj = 'List of Renewal Dates'
select @msg = @REG_NO + ', ' + @URN + ', ' + @OFFICER + ',' + @RENEWAL_DATE

print @recipient
print @sbj
print @msg

exec master..xp_sendmail @recipients= @recipient, @subject = @sbj,
@message=@msg

when it runs it will print out the name of the receipient - it looks like
outlook is kicking it back as being malformed.



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

Evaluation of conditions
complex and large triggers
Table design question on LARGE table
Query help
Any harm in adding a primary key...