|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with xp..sendmail needed!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 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 Show quote "Damon" <nonse***@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 Show quote "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" <nonse***@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 >> > > > Any more advice is greatly appreciated. Well, is it remotely possible that any of your e-mail addresses are longer > DECLARE @OFFICER_EMAIL as varchar(15) 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 Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eFP6phIDGHA.1312@TK2MSFTNGP09.phx.gbl... >> 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 Our systems use VARCHAR(128). Even I have e-mail addresses that are > 30 > the DECLARE @OFFICER_EMAIL as varchar(15). It should have been 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. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uXyqv1IDGHA.208@TK2MSFTNGP10.phx.gbl... >> 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... >  |
|||||||||||||||||||||||
Other interesting topics