Solved

Stored Procedure Email Que using sp_send_dbmail

Posted on 2010-11-18
19
1,024 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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
 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

680 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