It's soo close and almost there, but still not quite right.
The select you posted (poots) has stopped that error, and it now works, but the else part of the statement doesn't work.
The following errors are returned:
Quote:
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 65
A cursor with the name 'upcoming_cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 67
A cursor with the name 'upcoming_cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 87
A cursor with the name 'upcoming_cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_CRMActions2, Line 89
A cursor with the name 'upcoming_cursor' does not exist.
|
So I then copied the IF EXISTS and swapped the date filter around, which does get rid of the errors, but then I don't get any e-mails via the sendmail for actions that will be due in the future.
I only get e-mailed about actions that are overdue.
Code:
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- SET Variables
DECLARE @email nvarchar(50), @notes nvarchar(555), @subject nvarchar(75), @msg nvarchar(755)
IF EXISTS (SELECT NULL FROM dbo.tblActions WHERE dbo.tblActions.acti_complete = 0 AND dbo.tblActions.acti_due_datetime < GETDATE())
BEGIN
-- Set cursor to run through rows in query
DECLARE overdue_cursor CURSOR FOR
SELECT dbo.tblUsers.user_email, dbo.tblActions.acti_notes, dbo.tblActions.acti_subject
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime < GETDATE())
OPEN overdue_cursor
-- Get the values from the query and store them in the variables
FETCH NEXT FROM overdue_cursor INTO @email, @notes, @subject
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set the e-mail message
SET @msg = 'An action you created in the CRM is overdue.' + CHAR(13) + CHAR(10) +
'Please go back and sheck the status of your action. Here is a description of the action:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@subject + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@notes + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
'Regards,' + CHAR(13) + CHAR(10) +
'The MIS Team'
-- Send the e-mail
EXEC master.dbo.xp_sendmail
@recipients = @email,
@subject = 'You have an overdue action in the CRM which needs attention!',
@message = @msg;
-- Get values from next row
FETCH NEXT FROM overdue_cursor INTO @email, @notes, @subject
END
CLOSE overdue_cursor
DEALLOCATE overdue_cursor
END
ELSE
IF EXISTS (SELECT NULL FROM dbo.tblActions WHERE dbo.tblActions.acti_complete = 0 AND dbo.tblActions.acti_due_datetime > GETDATE())
BEGIN
-- Set cursor to run through rows in query
DECLARE upcoming_cursor CURSOR FOR
SELECT dbo.tblUsers.user_email, dbo.tblActions.acti_notes, dbo.tblActions.acti_subject
FROM dbo.tblActions INNER JOIN dbo.tblUsers ON dbo.tblActions.acti_user_id = dbo.tblUsers.user_id
WHERE (dbo.tblActions.acti_complete = 0) AND (dbo.tblActions.acti_due_datetime IS NOT NULL) AND (dbo.tblActions.acti_due_datetime > GETDATE())
OPEN upcoming_cursor
-- Get the values from the query and store them in the variables
FETCH NEXT FROM upcoming_cursor INTO @email, @notes, @subject
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set the e-mail message
SET @msg = 'An action you created in the CRM is almost due.' + CHAR(13) + CHAR(10) +
'Please go back and sheck the status of your action. Here is a description of the action:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@subject + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
@notes + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
'Regards,' + CHAR(13) + CHAR(10) +
'The MIS Team'
-- Send the e-mail
EXEC master.dbo.xp_sendmail
@recipients = @email,
@subject = 'You have an upcoming action in the CRM which needs attention!',
@message = @msg;
-- Get values from next row
FETCH NEXT FROM upcoming_cursor INTO @email, @notes, @subject
END
CLOSE upcoming_cursor
DEALLOCATE upcoming_cursor
END
END