December 8, 2010 at 8:56 am
I have writtent the following code to iterate through each of the rows in the recordset and send an email to the value held in the 'EmailSentTo' column, advising them that the task deadline date has expired.
BEGIN
SET NOCOUNT ON;
declare @PracticeIDint
declare @PracticeShortNamenvarchar(255)
declare @Categoryvarchar(50)
declare @SubCategoryvarchar(50)
declare @statusvarchar(50)
declare @AssignedTovarchar(50)
declare @Taskvarchar(max)
declare @Notevarchar(max)
declare @EmailSentTovarchar(50)
declare @DeadlineDatedate
declare item_cursor CURSOR FAST_FORWARD FOR
SELECTdbo.Tasks.PracticeId,
dbo.WareHousePractices.[Practice Short Name],
dbo.Tasks.Category,
dbo.Tasks.SubCategory,
dbo.Tasks.Status,
dbo.Tasks.AssignedTo,
dbo.Tasks.Task,
dbo.[Tasks-Notes].Note,
dbo.Tasks.EmailUpdateSentTo,
dbo.Tasks.DeadlineDate
FROMdbo.Tasks INNER JOIN
dbo.[Tasks-Notes] ON dbo.Tasks.RequirementKey = dbo.[Tasks-Notes].RequirmentsKey INNER JOIN
dbo.WareHousePractices ON dbo.Tasks.PracticeId = dbo.WareHousePractices.[Practice Id]
GROUP BYdbo.Tasks.PracticeId, dbo.WareHousePractices.[Practice Short Name], dbo.Tasks.Category, dbo.Tasks.SubCategory, dbo.Tasks.Status, dbo.Tasks.Task,
dbo.[Tasks-Notes].Note, dbo.Tasks.EmailUpdateSentTo, dbo.Tasks.AssignedTo, dbo.Tasks.DeadlineDate
HAVING(dbo.Tasks.Status <> 'Completed') AND (dbo.Tasks.PracticeId <> 0) AND (dbo.Tasks.PracticeId <> 1) AND (CONVERT(varchar(10), GETDATE(), 101)
= CONVERT(varchar(10), dbo.Tasks.DeadlineDate, 101))
ORDER BYdbo.Tasks.AssignedTo
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO
@PracticeID,
@PracticeShortName,
@Category,
@SubCategory,
@AssignedTo,
@Task,
@Note,
@EmailSentTo,
@DeadlineDate
WHILE @@FETCH_STATUS = 0
BEGIN
exec msdb.dbo.sp_send_dbmail @profile_name='ProofOfConceptMail', @recipients=@EmailSentTo , @subject='Task Deadline Expired', @body=@PracticeShortName
FETCH NEXT FROM item_cursor INTO
@PracticeID,
@PracticeShortName,
@Category,
@SubCategory,
@AssignedTo,
@Task,
@Note,
@EmailSentTo,
@DeadlineDate
END
close item_cursor
deallocate item_cursor
END
On execution, I get the following;
Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 260
At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Why does it not allow the use of a variable in the @recipients parameter? Is there another way to achieve the desired functionality?
December 8, 2010 at 9:17 am
Is it possible there's a null value being passed to the @recipients parameter? Check the cursor's query, see if any rows will be null on that column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 9:22 am
Indeed it was! A schoolboy error! Thanks for the prompt reply.
December 8, 2010 at 9:23 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2012 at 10:56 am
I got the same error but don't have NULL values in the email address column. Any help is much appreciated.
Thanks,
Prakash
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply