Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can you please help?

Author  Topic 

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-24 : 11:54:09
This code is written with CURSOR but I would like to change it to non-cursor.

Can anyone please help?

ALTER PROCEDURE [dbo].[RecoverPassword]
@LoginEmail  nvarchar(50) = ''
 AS

 BEGIN
SET NOCOUNT ON;

 DECLARE pwd_Cursor CURSOR FOR


SELECT TOP 1 LoginId,
  FullName,
   email,
   Password
 FROM Users
 WHERE email = @LoginEmail
 ORDER BY LoginId DESC

 OPEN pwd_Cursor
 Declare @LoginId int
 Declare @fullname nvarchar(100)
 Declare @email nvarchar(MAX)
 Declare @password nvarchar(20)
 -- Get the current MAX ID
 Declare @mailID as int
  -- Start reading each record from the cursor.
 FETCH pwd_Cursor into @LoginId,@fullname,@email,@password
 WHILE @@FETCH_STATUS = 0
  BEGIN

  INSERT into EmailReminders (mailContent,
                                 LoginId,
                                 FullName,
                                 email,
                                 Password,
                                 sender,
                                 Sent)
 VALUES (
'This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.

Dear '+@FullName+':

Please check your Mail Box for the password..

The Mail has been sent with your password and should be in your inbox within the next 10 minutes.

If you do not receive your email within 10 minutes, please check your junk mail box.

Your UserName is: '+@email+'.
 
Your Password is: '+@password+'.

Once you have retrieved your login information, please click the link below to get back to the Poll Worker Training login screen.



Regards,
The Registras Office.',
 @LoginId,
 @FullName,
 @email,
 @Password,
  'Administrator',
'No'
 )
  
 FETCH pwd_Cursor into @LoginId,@FullName,@email,@password
 END
  
 CLOSE pwd_Cursor
 DEALLOCATE pwd_Cursor
 END
   
 BEGIN
 DECLARE MAIL_CURSOR CURSOR FOR
  
 select mailid, sender, mailcontent
 from EmailReminders
 where Sent = 'No'
   
 Declare @mail1 int
 Declare @sender nvarchar(100)
 declare @content1 nvarchar(4000)
  
 OPEN MAIL_CURSOR
  
 FETCH MAIL_CURSOR into @mail1, @sender,@content1
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
    -- SET  @email=''
	 SELECT top 1 @email = @email+';'+Email
	 	FROM EmailReminders
    WHERE sent = 'No'
 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registras Office', -- your defined email profile or static info
@recipients = @email, -- your email
@subject = 'Your Account Details',
@body = @content1;

--Update the record in EmailReminders table where Sent = 'No'.

Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No' and mailid = @mail1
 
 FETCH MAIL_CURSOR INTO @mail1, @sender, @content1
 END
  
 CLOSE MAIL_CURSOR
 DEALLOCATE MAIL_CURSOR
 END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-24 : 12:09:21
here you go


ALTER PROCEDURE [dbo].[RecoverPassword]
@LoginEmail  nvarchar(50) = ''
 AS

 BEGIN
SET NOCOUNT ON;

  INSERT into EmailReminders (mailContent,
                                 LoginId,
                                 FullName,
                                 email,
                                 Password,
                                 sender,
                                 Sent)
 SELECT
'This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.

Dear '+ FullName +':

Please check your Mail Box for the password..

The Mail has been sent with your password and should be in your inbox within the next 10 minutes.

If you do not receive your email within 10 minutes, please check your junk mail box.

Your UserName is: '+ email +'.
 
Your Password is: '+ password +'.

Once you have retrieved your login information, please click the link below to get back to the Poll Worker Training login screen.



Regards,
The Registras Office.',
 LoginId,
 FullName,
 email,
 Password,
 'Administrator',
 'No'
FROM Users
WHERE email = @LoginEmail
ORDER BY LoginId DESC

  
 Declare @email nvarchar(MAX)
   
 SELECT @email = STUFF((SELECT ';' + Email
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,'')
 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registras Office', -- your defined email profile or static info
@recipients = @email, -- your email
@subject = 'Your Account Details',
@body = @content1;



Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No' and mailid = @mail1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-24 : 12:39:45
WOW!

Thank you soo much visakh16.

What a wonderful surprise.

I am very grateful for your assistance.

I am testing it now.
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-24 : 13:01:21
Do I need to declare these:

@LoginId,
@FullName,
@email,
@Password,
before AS because I am getting error that they are not declared.

Maybe something like this:

CREATE PROCEDURE [dbo].[RecoverPassword]
@LoginEmail nvarchar(50)
AS
Declare @LoginId integer,
Declare @FullName nvarchar(50),
Declare @email nvarchar(50),
Declare @Password nvarchar(50)
BEGIN
SET NOCOUNT ON;
...
...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-24 : 13:10:11
quote:
Originally posted by simflex

Do I need to declare these:

@LoginId,
@FullName,
@email,
@Password,
before AS because I am getting error that they are not declared.

Maybe something like this:

CREATE PROCEDURE [dbo].[RecoverPassword]
@LoginEmail nvarchar(50)
AS
Declare @LoginId integer,
Declare @FullName nvarchar(50),
Declare @email nvarchar(50),
Declare @Password nvarchar(50)
BEGIN
SET NOCOUNT ON;
...
...

i've not used any of the above variables
please copy and paste my posted code rather than editing your earlier query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-24 : 14:25:28
When I run the stored proc, it compiles just fine but is no longer showing the following messages:

Dear '+ FullName +':

Please check your Mail Box for the password..

The Mail has been sent with your password and should be in your inbox within the next 10 minutes.

If you do not receive your email within 10 minutes, please check your junk mail box.

Your UserName is: '+ email +'.

Your Password is: '+ password +'.

Once you have retrieved your login information, please click the link below to get back to the login screen

Any ideas?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-25 : 02:56:19
do you've any records returned by above select based on your passed LoginEmail value?


SELECT
 LoginId,
 FullName,
 email,
 Password
FROM Users
WHERE email = @LoginEmail
ORDER BY LoginId DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-25 : 08:52:48
Visakh16,

The part that inserts into EmailReminders works great.

The problem is that when the email is sent using sp_send_dbMail, the contents of the email that was inserted into EmailReminders was not sent along with the email.

With the CURSOR, you get the email that thats with:

This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.

Dear John Doe:
and the the rest

The new version only shows the name of the recipient and the body is blank.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-25 : 08:55:52
Where are you getting @Content1 value from?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-25 : 09:46:04
visakh16,
with the CURSOR version, this snip will handle content1

FETCH pwd_Cursor into @LoginId,@FullName,@email,@password
 END
  
 CLOSE pwd_Cursor
 DEALLOCATE pwd_Cursor
 END
   
 BEGIN
 DECLARE MAIL_CURSOR CURSOR FOR
  
 select mailid, sender, mailcontent
 from EmailReminders
 where Sent = 'No'
   
 Declare @mail1 int
 Declare @sender nvarchar(100)
 declare @content1 nvarchar(4000)
  
 OPEN MAIL_CURSOR
  
 FETCH MAIL_CURSOR into @mail1, @sender,@content1
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
    -- SET  @email=''
	 SELECT top 1 @email = @email+';'+Email
	 	FROM EmailReminders
    WHERE sent = 'No'
 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registras Office', -- your defined email profile or static info
@recipients = @email, -- your email
@subject = 'Your Account Details',
@body = @content1;

--Update the record in EmailReminders table where Sent = 'No'.

Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No' and mailid = @mail1
 
 FETCH MAIL_CURSOR INTO @mail1, @sender, @content1
 END
  
 CLOSE MAIL_CURSOR
 DEALLOCATE MAIL_CURSOR
 END


I don't know where to put content1 in your version of code.
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-25 : 12:04:11
Also, sir, the Update is not updating either.

Thank you very much for all your help sir.
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-28 : 18:14:33
Hello, SQL Gurus,

Is there anyone who can assist me with this issue?

There are actually 3 issues with the SQL that visakh16(thanks again for the attempts).

Issue 1, visakh16's version sends email regardless of whether the email address is legitimate or not. It just sends it to the available legitimate email address.

This tells me that the loginEmail parameter is not working.

Issue 2, the Update statement is not working.

The emailReminder table is supposed to be updated with sent set to Yes after email is sent.

Issue 3, the message content is not being displayed when email goes out.

My version, the CURSOR version, sends out email if the email address legitimate. It doesn't send email if email address isn't valid.

By valid, I mean that if the email address doesn't exist on our db, it doesn't send reminders. It does send reminders if email is on db.

The old version also displays content as shown on the reminder.

The reason, I am trying to rewrite the cursor version to regular sql version is so I can out message output.

This way, if email address is valid, and email is sent out, message is displayed that email reminder has been sent out.

If email is not on db, message is displayed to user that email is not found.

If I can fix my version to handle this messaging problem, I would greatly and gladly use that version.

Thanks a lot in advance for any assistance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-29 : 00:10:03
quote:

Issue 1, visakh16's version sends email regardless of whether the email address is legitimate or not. It just sends it to the available legitimate email address.

This tells me that the loginEmail parameter is not working.
I cant see any check in your code for legitimate emails. I'm using same logic in my version too as per your version with only changes being outside cursor so dont understand how it wont work!
Issue 2, the Update statement is not working.

The emailReminder table is supposed to be updated with sent set to Yes after email is sent.
My code has the update in the end
i think you can dispense with mail filter condition
ie this

Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No'


Issue 3, the message content is not being displayed when email goes out.
i need it needs a small tweak for that



try this modified code as per above


ALTER PROCEDURE [dbo].[RecoverPassword]
@LoginEmail  nvarchar(50) = ''
 AS

 BEGIN
SET NOCOUNT ON;

  INSERT into EmailReminders (mailContent,
                                 LoginId,
                                 FullName,
                                 email,
                                 Password,
                                 sender,
                                 Sent)
 SELECT
'This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.

Dear '+ FullName +':

Please check your Mail Box for the password..

The Mail has been sent with your password and should be in your inbox within the next 10 minutes.

If you do not receive your email within 10 minutes, please check your junk mail box.

Your UserName is: '+ email +'.
 
Your Password is: '+ password +'.

Once you have retrieved your login information, please click the link below to get back to the Poll Worker Training login screen.



Regards,
The Registras Office.',
 LoginId,
 FullName,
 email,
 Password,
 'Administrator',
 'No'
FROM Users
WHERE email = @LoginEmail
ORDER BY LoginId DESC

  
 Declare @email nvarchar(MAX),@content1 nvarchar(4000)
   
 SELECT @email = STUFF((SELECT ';' + Email
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,''),
       @content1 = STUFF((SELECT ';' + mailcontent
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,'')
 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registras Office', -- your defined email profile or static info
@recipients = @email, -- your email
@subject = 'Your Account Details',
@body = @content1;



Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-29 : 12:49:43
Thank you very much visakh16.

Works much, much better now.

First, now, it sends email when email address is legitimate but doesn't send one when it isn't.

It just sends blank email when someone is copied - when email is invalid.

I can live with this.

There are some issues I would hope you would have a minute to assist with if you can.

One, now, when the email goes out, the content has something like this:

#x0D

I suspect those happen when you hit the enter for new line because it shows it one with one new line and twice when you hit enter twice for double spacing.

Any ideas what I could try to stop this?

I tried @body_format='HTML' and all it did was bunch everything together into one line which wraps indiscriminately.

The other request, if possible is what part of the code should I include validation in such that if email address supplied by user is invalid (doesn't exist on db), user will be alerted?

Again, thanks so much for your assistance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-29 : 14:08:45
The other request, if possible is what part of the code should I include validation in such that if email address supplied by user is invalid (doesn't exist on db), user will be alerted?
One, now, when the email goes out, the content has something like this:

#x0D

I suspect those happen when you hit the enter for new line because it shows it one with one new line and twice when you hit enter twice for double spacing.

Any ideas what I could try to stop this?

this?


ALTER PROCEDURE [dbo].[RecoverPassword]
@LoginEmail  nvarchar(50) = ''
 AS

 BEGIN
SET NOCOUNT ON;

 IF NOT EXISTS (SELECT 1 FROM Users
WHERE email = @LoginEmail)
   RAISERROR 'Invalid Email Passed',16,1

  INSERT into EmailReminders (mailContent,
                                  LoginId,
                                 FullName,
                                 email,
                                 Password,
                                 sender,
                                 Sent)
 SELECT
'This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.

Dear '+ FullName +':

Please check your Mail Box for the password..

The Mail has been sent with your password and should be in your inbox within the next 10 minutes.

If you do not receive your email within 10 minutes, please check your junk mail box.

Your UserName is: '+ email +'.
 
Your Password is: '+ password +'.

Once you have retrieved your login information, please click the link below to get back to the Poll Worker Training login screen.



Regards,
The Registras Office.',
 LoginId,
 FullName,
 email,
 Password,
 'Administrator',
 'No'
FROM Users
WHERE email = @LoginEmail
ORDER BY LoginId DESC

  
 Declare @email nvarchar(MAX),@content1 nvarchar(4000)
   
 SELECT @email = STUFF((SELECT ';' + Email
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,''),
       @content1 = STUFF((SELECT ';' + REPLACE(mailcontent,'#x0D',CHAR(10) + CHAR(13))
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,'')
 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registras Office', -- your defined email profile or static info
@recipients = @email, -- your email
@subject = 'Your Account Details',
@body = @content1;



Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-30 : 10:26:48
Hi visakh16,

I didn't want to seem like I "disappeared" without appropriately thanking you for your help and patience.
Thank you very much.0

I tested your new code yesterday but same result.

Still getting those #x0D. Actually they should be #x0D. My bad, sorry but I changed it anyway and still have same result.

And even though the raiseError works in terms of catching when catching when email address is valid or not, it still sends email when email is invalid.

The message says, "Invalid Email Passed Emailed Queued"

I am not sure what the additional message but I researched it but couldn't find anything useful.

It is ok if you don't want to waste your time on this anymore.

You have done more than I asked for.

Thanks again for all the help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-30 : 12:19:13
what about this small tweak


ALTER PROCEDURE [dbo].[RecoverPassword]
@LoginEmail  nvarchar(50) = ''
 AS

 BEGIN
SET NOCOUNT ON;

 IF NOT EXISTS (SELECT 1 FROM Users
WHERE email = @LoginEmail)
BEGIN
   RAISERROR 'Invalid Email Passed',16,1
   RETURN
END
  INSERT into EmailReminders (mailContent,
                                  LoginId,
                                 FullName,
                                 email,
                                 Password,
                                 sender,
                                 Sent)
 SELECT
'This is a computer generated email message.
Please DO NOT use the REPLY button above to respond to this email.

Dear '+ FullName +':

Please check your Mail Box for the password..

The Mail has been sent with your password and should be in your inbox within the next 10 minutes.

If you do not receive your email within 10 minutes, please check your junk mail box.

Your UserName is: '+ email +'.
 
Your Password is: '+ password +'.

Once you have retrieved your login information, please click the link below to get back to the Poll Worker Training login screen.



Regards,
The Registras Office.',
 LoginId,
 FullName,
 email,
 Password,
 'Administrator',
 'No'
FROM Users
WHERE email = @LoginEmail
ORDER BY LoginId DESC

  
 Declare @email nvarchar(MAX),@content1 nvarchar(4000)
   
 SELECT @email = STUFF((SELECT ';' + Email
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,''),
       @content1 = STUFF((SELECT ';' + REPLACE(mailcontent,'#x0D',CHAR(10) + CHAR(13))
	 	        FROM EmailReminders
                        WHERE sent = 'No'
                        FOR XML PATH('')),1,1,'')
 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registras Office', -- your defined email profile or static info
@recipients = @email, -- your email
@subject = 'Your Account Details',
@body = @content1;



Update EmailReminders SET Sent = 'Yes' WHERE Sent = 'No'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-30 : 14:35:18
One step closer to perfect solution.

I enter an invalid email and it raises the "Invalid Email Passed" error and no email is sent.

This is perfect.

I enter an email that exists and user is alerted that email has been sent out successfully. The email is actually received.

This is perfect.

Once the email is sent, EmailReminder table is updated successfully from sent='No' to sent='Yes'

This is perfect too!!!

Only stubborn issue to refuses to go away is the weird characters that just keeps coming.

It now reads: #x0D

If all else fails, I can let the user deal with that. It does show the password retrieval the user is after. It just adds the weird characters. They are more of a nuisance anyway.

I am truly grateful for your patience and continued assistance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-10-31 : 00:32:22
Ok..one question. Is the content passed to email actually a XML document?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-31 : 10:13:28
No, the content is dumped to email as is.

Once my .net app invokes the stored proc, it just grabs the contents from EmailReminders table and displays the contents of content1 to email body.

With the CURSOR version, this issue didin't exist.

Just based on your question, I am now beginning to wonder if the FOR XML Path syntax could be the culprit.

Can it rewritten with the FOR XML Path bit so we can try that?

Thank you so much visakh16 for all your assistance.
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2013-10-31 : 10:35:24
Hi visakh16, Yes, the FOR XML PATH was the problem.

I made a slight change to the SELECT:

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

Now, it WORKS perfectly.

You have been incredibly helpful and more importantly, your patience is without equal.

You stayed until you me through with solutions.

THANK YOU VERY MUCH.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site