email being sent, but no message

I have setup an email notifications system, that basically takes each
row from a table and sents out an email according to the data in that
row. The emails get sent, with the subject being filled as expected.
Only problem is that sometimes there is no message.

Here is the stored procedure that is being called every hour to send
the emails:

CREATE PROCEDURE dbo.RemindersSendEmails AS

--Cursor
DECLARE RemindersCursor CURSOR FOR
SELECT *
FROM RemindersTodaysAndUnsent

--Values for cursor
DECLARE
@.I_Reminder_ID bigint,
@.I_Notice_ID bigint,
@.V_Reminder_Text varchar(250),
@.SDT_Reminder_Date smalldatetime,
@.V_Email varchar(50),
@.I_Reminder_Type bigint,
@.SDT_Reminder_Sent smalldatetime,
@.I_Attempts_Made int,
@.V_Notice_Type varchar(50),
@.I_Notice_Period int,
@.V_Period_Description varchar(50),
@.I_Project_ID bigint,
@.V_Notice_Ref varchar(10)

--values for sending the mail
DECLARE @.NEWLINE varchar(2)

OPEN RemindersCursor

FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
--INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
--@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
--@.I_Project_ID, @.V_Notice_Ref

SET @.NEWLINE = char(10)

--PRINT 'start'

WHILE @.@.FETCH_STATUS = 0
BEGIN

DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
int

SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
Notice Ref ' + RTRIM(@.V_Notice_Ref)

SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
@.I_Project_ID)) + @.NEWLINE +
'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
RTRIM(@.V_Period_Description) + @.NEWLINE +
'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
@.NEWLINE +
'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
'Email sent to: ' + @.V_Email + @.NEWLINE +
'Number of attempts made at sending this email (once every hour): '
+ CONVERT(varchar(4), @.I_Attempts_Made)
--@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
@.I_Notice_Period,

PRINT 'subject = ' + @.Subject
PRINT 'message = ' + @.EmailMessage

SET @.V_Email = LTRIM(RTRIM(@.V_Email))

EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
@.message = @.EmailMessage,
@.subject = @.Subject

--PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
@.Status)

--If send mail is a success
IF (@.Status = 0)
BEGIN
UPDATE Reminders
SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
@.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END
--Else send mail failed
ELSE
BEGIN
UPDATE Reminders
SET I_Attempts_Made = @.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END

-- Get the next reminder
FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
END

--PRINT 'End'

CLOSE RemindersCursor
DEALLOCATE RemindersCursor
GOJust a guess-- I'd check to see if any of your variables are NULL the SET
statement that builds your @.EmailMessage variable. In that case maybe your
entire @.EmailMessage variable is getting set to NULL?

"Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> I have setup an email notifications system, that basically takes each
> row from a table and sents out an email according to the data in that
> row. The emails get sent, with the subject being filled as expected.
> Only problem is that sometimes there is no message.
> Here is the stored procedure that is being called every hour to send
> the emails:
> CREATE PROCEDURE dbo.RemindersSendEmails AS
> --Cursor
> DECLARE RemindersCursor CURSOR FOR
> SELECT *
> FROM RemindersTodaysAndUnsent
> --Values for cursor
> DECLARE
> @.I_Reminder_ID bigint,
> @.I_Notice_ID bigint,
> @.V_Reminder_Text varchar(250),
> @.SDT_Reminder_Date smalldatetime,
> @.V_Email varchar(50),
> @.I_Reminder_Type bigint,
> @.SDT_Reminder_Sent smalldatetime,
> @.I_Attempts_Made int,
> @.V_Notice_Type varchar(50),
> @.I_Notice_Period int,
> @.V_Period_Description varchar(50),
> @.I_Project_ID bigint,
> @.V_Notice_Ref varchar(10)
> --values for sending the mail
> DECLARE @.NEWLINE varchar(2)
> OPEN RemindersCursor
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> -- @.I_Project_ID, @.V_Notice_Ref
> SET @.NEWLINE = char(10)
> --PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> int
> SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> Notice Ref ' + RTRIM(@.V_Notice_Ref)
> SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> @.I_Project_ID)) + @.NEWLINE +
> 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> RTRIM(@.V_Period_Description) + @.NEWLINE +
> 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> @.NEWLINE +
> 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> 'Email sent to: ' + @.V_Email + @.NEWLINE +
> 'Number of attempts made at sending this email (once every hour): '
> + CONVERT(varchar(4), @.I_Attempts_Made)
> --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> @.I_Notice_Period,
> PRINT 'subject = ' + @.Subject
> PRINT 'message = ' + @.EmailMessage
> SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> @.message = @.EmailMessage,
> @.subject = @.Subject
> --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> @.Status)
> --If send mail is a success
> IF (@.Status = 0)
> BEGIN
> UPDATE Reminders
> SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
> --Else send mail failed
> ELSE
> BEGIN
> UPDATE Reminders
> SET I_Attempts_Made = @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
>
> -- Get the next reminder
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> END
> --PRINT 'End'
> CLOSE RemindersCursor
> DEALLOCATE RemindersCursor
> GO|||"MissLivvy" <XeveryidiwantistakenX@.yahoo.com> wrote in message
news:jeRqd.3625$u81.3215@.newsread3.news.pas.earthl ink.net...
> Just a guess-- I'd check to see if any of your variables are NULL the SET
> statement that builds your @.EmailMessage variable. In that case maybe your
> entire @.EmailMessage variable is getting set to NULL?

Ah good one.

This got me once. I forgot abuot it.

> "Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
> news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> > I have setup an email notifications system, that basically takes each
> > row from a table and sents out an email according to the data in that
> > row. The emails get sent, with the subject being filled as expected.
> > Only problem is that sometimes there is no message.
> > Here is the stored procedure that is being called every hour to send
> > the emails:
> > CREATE PROCEDURE dbo.RemindersSendEmails AS
> > --Cursor
> > DECLARE RemindersCursor CURSOR FOR
> > SELECT *
> > FROM RemindersTodaysAndUnsent
> > --Values for cursor
> > DECLARE
> > @.I_Reminder_ID bigint,
> > @.I_Notice_ID bigint,
> > @.V_Reminder_Text varchar(250),
> > @.SDT_Reminder_Date smalldatetime,
> > @.V_Email varchar(50),
> > @.I_Reminder_Type bigint,
> > @.SDT_Reminder_Sent smalldatetime,
> > @.I_Attempts_Made int,
> > @.V_Notice_Type varchar(50),
> > @.I_Notice_Period int,
> > @.V_Period_Description varchar(50),
> > @.I_Project_ID bigint,
> > @.V_Notice_Ref varchar(10)
> > --values for sending the mail
> > DECLARE @.NEWLINE varchar(2)
> > OPEN RemindersCursor
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > -- @.I_Project_ID, @.V_Notice_Ref
> > SET @.NEWLINE = char(10)
> > --PRINT 'start'
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> > int
> > SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> > Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> > Notice Ref ' + RTRIM(@.V_Notice_Ref)
> > SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> > @.I_Project_ID)) + @.NEWLINE +
> > 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> > 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> > RTRIM(@.V_Period_Description) + @.NEWLINE +
> > 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> > 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> > @.NEWLINE +
> > 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> > 'Email sent to: ' + @.V_Email + @.NEWLINE +
> > 'Number of attempts made at sending this email (once every hour): '
> > + CONVERT(varchar(4), @.I_Attempts_Made)
> > --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> > @.I_Notice_Period,
> > PRINT 'subject = ' + @.Subject
> > PRINT 'message = ' + @.EmailMessage
> > SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> > EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> > @.message = @.EmailMessage,
> > @.subject = @.Subject
> > --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> > @.Status)
> > --If send mail is a success
> > IF (@.Status = 0)
> > BEGIN
> > UPDATE Reminders
> > SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> > @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > --Else send mail failed
> > ELSE
> > BEGIN
> > UPDATE Reminders
> > SET I_Attempts_Made = @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > -- Get the next reminder
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > END
> > --PRINT 'End'
> > CLOSE RemindersCursor
> > DEALLOCATE RemindersCursor
> > GO