Solved

Stored Procedure Email Que using sp_send_dbmail

Posted on 2010-11-18
19
1,012 Views
Last Modified: 2012-05-10
Hi,

Was wondering if anyone had a sample stored procedure code that relates to what I'm trying to do.

I have a SQL table called MailQueue

with fields

MailQueue
- FromName
- FromAddress
- ToName
- ToAddress
- Subject
- Body
- HasSent
- DateStamp

What I'm trying to do is write a stored procedure that will send all email's with HasSent = 0 using sp_send_dbmail

Once the email goes out, I need to do an update Query that changes HasSent = 1

finally, one more SQL query that deletes all emails with hasSent = 1 that is older then 3 months.

I found some sample code and tried modifying it, but it's a mess.

I'm not very good with stored procedures, so any help would be greatly appreciated.

here is what I've started
 
USE [ReefJunkies]
GO
/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MailQue]

AS


   
DECLARE @EmailRecipient VARCHAR(50);
DECLARE db_cursor CURSOR FOR
SET @EmailRecipient = (SELECT ToAddress from dbo.MailQueue WHERE (HasSent=0));
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
 
WHILE @@FETCH_STATUS=0
BEGIN
 

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailRecipient,
    @subject = 'Subject',
    @body = 'body',
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

FETCH NEXT FROM db_cursor INTO @EmailRecipient;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90

Open in new window

0
Comment
Question by:TheWebGuy38
  • 9
  • 8
19 Comments
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34162675
If it's working as expected then its good except some preformance best practices. Check some more performance tips here:

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/
0
 

Author Comment

by:TheWebGuy38
ID: 34162941
errr update.

I know I'm close, but not working yet.
also screwed up the curser which I don't really understand.

updated code

anyone, anyone? :)
USE [ReefJunkies]

GO

/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Proc_MailQue]



AS



SET NOCOUNT ON;



DECLARE @MailqueueID int;

DECLARE @FromName VARCHAR(max);

DECLARE @FromAddress VARCHAR(max);

DECLARE @ToName VARCHAR(max);

DECLARE @ToAddress VARCHAR(max);

DECLARE @Subject VARCHAR(max);

DECLARE @Body VARCHAR(max);

DECLARE @HasSent int;

DECLARE @DateStamp Datetime;





/*DECLARE db_cursor CURSOR FOR */

SELECT @MailqueueID = MailqueueID, @FromName = FromName, @FromAddress = FromAddress, @ToName = ToName, @ToAddress = ToAddress, @Subject = Subject, @Body = Body, @HasSent = HasSent, @DateStamp = DateStamp from dbo.MailQueue WHERE (HasSent=0) Order by MailQueueID asc

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @ToAddress;

 

WHILE @@FETCH_STATUS=0

BEGIN

 



 

EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,

    @from_address = @FromAddress,

    @subject = @Subject,

    @body = @Body,

    @body_format = 'HTML',

@profile_name='Reef Junkies';





UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)



FETCH NEXT FROM db_cursor INTO @ToAddress;

END

 

CLOSE db_cursor;

DEALLOCATE db_cursor;



DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90

   

SET NOCOUNT OFF;

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163216
Check out this.....

your cursor declaration was not proper.
--USE [ReefJunkies]
GO
/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MailQue]

AS

SET NOCOUNT ON;

DECLARE @MailqueueID int;
DECLARE @FromName VARCHAR(max);
DECLARE @FromAddress VARCHAR(max);
DECLARE @ToName VARCHAR(max);
DECLARE @ToAddress VARCHAR(max);
DECLARE @Subject VARCHAR(max);
DECLARE @Body VARCHAR(max);
DECLARE @HasSent int;
DECLARE @DateStamp Datetime;


DECLARE db_cursor CURSOR FOR
SELECT MailqueueID, FromName, FromAddress, ToName, ToAddress, Subject, Body, HasSent, DateStamp 
from dbo.MailQueue 
WHERE (HasSent=0) 
Order by MailQueueID asc

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@Subject,@Body,@HasSent,@DateStamp ;
 
WHILE @@FETCH_STATUS=0
BEGIN
 

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @Subject,
    @body = @Body,
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@Subject,@Body,@HasSent,@DateStamp ;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
   
SET NOCOUNT OFF;

Open in new window

0
 

Author Comment

by:TheWebGuy38
ID: 34163316
hrmmmmmm

this line "DECLARE db_cursor CURSOR FOR" if not rem'd out throws an error

"
Msg 154, Level 15, State 3, Procedure Proc_MailQue, Line 20
variable assignment is not allowed in a cursor declaration."

line 20 = DECLARE @Body VARCHAR(max);

any ideas?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163380
hi

might be it clashing with

EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @Subject,
    @body = @Body,
    @body_format = 'HTML',
@profile_name='Reef Junkies';


@body = @body-

try to change @varbody
0
 

Author Comment

by:TheWebGuy38
ID: 34163468
hrmmmmm

nope that wasn't it. changed @body to @varbody but got the same exact error
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163484
try this....

EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @Subject,
    @body = 'Body',
    @body_format = 'HTML',
@profile_name='Reef Junkies';
0
 

Author Comment

by:TheWebGuy38
ID: 34163500
nope, same thing
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163507
can u post ur new code...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:TheWebGuy38
ID: 34163513
Sure

here it is
USE [ReefJunkies]

GO

/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Proc_MailQue]



AS



SET NOCOUNT ON;



DECLARE @MailqueueID int;

DECLARE @FromName VARCHAR(max);

DECLARE @FromAddress VARCHAR(max);

DECLARE @ToName VARCHAR(max);

DECLARE @ToAddress VARCHAR(max);

DECLARE @Subject VARCHAR(max);

DECLARE @Body VARCHAR(max);

DECLARE @HasSent int;

DECLARE @DateStamp Datetime;





DECLARE db_cursor CURSOR FOR



SELECT @MailqueueID = MailqueueID, @FromName = FromName, @FromAddress = FromAddress, @ToName = ToName, @ToAddress = ToAddress, @Subject = Subject, @Body = Body, @HasSent = HasSent, @DateStamp = DateStamp from dbo.MailQueue WHERE (HasSent=0) Order by MailQueueID asc



OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@Subject,@Body,@HasSent,@DateStamp;

 

 

WHILE @@FETCH_STATUS=0

BEGIN



 



 

EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,

    @from_address = @FromAddress,

    @subject = @Subject,

    @body = @Body,

    @body_format = 'HTML',

@profile_name='Reef Junkies';





UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)



FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@Subject,@Body,@HasSent,@DateStamp ;

END

 

CLOSE db_cursor;

DEALLOCATE db_cursor;







DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90

   

SET NOCOUNT OFF;

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 34163565
Hi,
then it seems you didnt update what i said.

1st try below code.
USE [ReefJunkies]
GO
/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MailQue]

AS

SET NOCOUNT ON;

DECLARE @MailqueueID int;
DECLARE @FromName VARCHAR(max);
DECLARE @FromAddress VARCHAR(max);
DECLARE @ToName VARCHAR(max);
DECLARE @ToAddress VARCHAR(max);
DECLARE @varSubject VARCHAR(max);
DECLARE @varBody VARCHAR(max);
DECLARE @HasSent int;
DECLARE @DateStamp Datetime;


DECLARE db_cursor CURSOR FOR
SELECT MailqueueID, FromName, FromAddress, ToName, ToAddress, Subject, Body, HasSent, DateStamp 
from dbo.MailQueue 
WHERE (HasSent=0) 
Order by MailQueueID asc

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@varSubject,@varBody,@HasSent,@DateStamp;
 
 
WHILE @@FETCH_STATUS=0
BEGIN

 

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @varSubject,
    @body = @varBody,
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@varSubject,@varBody,@HasSent,@DateStamp ;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;



DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
   
SET NOCOUNT OFF;

Open in new window

0
 

Author Comment

by:TheWebGuy38
ID: 34163593
I did, I just changed it back when it didn't work.

let me try your code
0
 

Author Comment

by:TheWebGuy38
ID: 34163640
Oh man!!!!!!!!! Your friggen Awesome!!!!

it works great! You are the SQL master! I really appreciate your help
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163728


:-)

It feels me good when I can assist someone....

enjoyy
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34221922
Sir,

If you got the answer then Please accept the solution. :-)
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34226077

Mr.Webguy

You accpted my wrong post. :-)
Please accpt appropriate post
0
 

Author Comment

by:TheWebGuy38
ID: 34227224
can't for the life of me figure out how to get the "accept solution" back
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34227359
Hi.
You can reopen Question by clicking Request Attn.Link in top.

Request moderator to reopen the Question.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now