0

This is probably a simple problem but I just can't see it.

We have two tables, Login table and Notifications table.

Each time a user wishes to recover his or her password, the user opens up the app to the browser, enters his or her email address and clicks the Recover password button.

If the user does not exist on the database, a message is displayed to the user advising him or her that his or her account doesn't exist on our database. This works fine.

If the info exists s/he is supposed to receive an email containing his or her password. This is where the problem is.

The user is not receiving an email at all.

In the code below, user's info is extracted from login table and inserted into notifications table.

Then the Notifications table is queried and email sent out to users from this table.

Why are we doing it this way?

Because the database is outside our firewall and there is no way to send an email from the location of the app.

Our only option is to use dbmail.

My question is why isn't this stored procedure sending out email?

It only sends to me because my email address is hardcoded.

My code is below and your assistance is greatly appreciated.

ALTER PROCEDURE [dbo].[ForgorttenPWD]
@LoginEmail  nvarchar(50) = ''
 AS    
 BEGIN
SET NOCOUNT ON;
 IF NOT EXISTS (SELECT 1 FROM [tblLogin]
WHERE email = @LoginEmail)
BEGIN
   RAISERROR ('The email address you entered does not exist on our database. Please try another email address',16,1)
   RETURN
END
INSERT into Notifications(mailContent, LoginId, FullName, email, Password, sender, Sent)
 SELECT
'This is a computer generated email message.
Dear '+ FullName +':
Please check your Mail Box for the password..
Your UserName is: '+ email +'.
Your Password is: '+ password +', 
 LoginId,
 FullName,
 email,
 Password,
  'NoReply@myemail.com',
'No'
FROM [tblLogin]
WHERE email = @LoginEmail
ORDER BY LoginId DESC

 Declare @email nvarchar(MAX),@content1 nvarchar(4000)
     SELECT top 1 @email = @email+';'+Email, @content1 = mailcontent
        FROM Notifications
    WHERE sent = 'No' and email=@LoginEmail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registrations Office', -- defined email profile or static info
@recipients = @email, -- your email
@blind_copy_recipients = 'myemail@mycompany.com',
@subject = 'Your Account Details',
@body = @content1;
Update Notifications SET Sent = 'Yes' WHERE Sent = 'No'
END
wallyk
  • 56,922
  • 16
  • 83
  • 148
Chidi Okeh
  • 1,537
  • 8
  • 28
  • 50
  • Are some quotes missing? Or maybe I munged them when I abbreviated the content? – wallyk Jan 09 '14 at 18:35
  • If you add a 'print @email' statment and run the stored procedure manually are you getting the proper value? – Kevin Kunderman Jan 09 '14 at 18:35
  • When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the msg. You can see full details in table msdb.dbo.sysmail_mailitems. – DOK Jan 09 '14 at 18:36
  • Incidentally, the maximum length of a valid email address is 254 characters: [What is the maximum length of a valid email address?](http://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address). – Andrew Morton Jan 09 '14 at 18:46

1 Answers1

1

I suspect the following is happening. In your code, you are declaring, 

  Declare @email nvarchar(MAX).

And immediately below it you are concatenating it with Email column value

..."SELECT top 1 @email = @email+';'+Email, @content1 = mailcontent FROM Notifications WHERE sent = 'No' and email=@LoginEmail...."

at this point @email will become null because null + any value is null.

Please try the following:

 Declare @email nvarchar(MAX),@content1 nvarchar(4000)
 select @email = '', @content1 = '' -- this is the key

 SELECT top 1 @email = @email+';'+Email, @content1 = mailcontent
 FROM Notifications
 WHERE sent = 'No' and email=@LoginEmail
Consult Yarla
  • 1,150
  • 10
  • 22