• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1047
  • Last Modified:

Stored Procedure Email Que using sp_send_dbmail

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
TheWebGuy38
Asked:
TheWebGuy38
  • 9
  • 8
1 Solution
 
subhashpuniaCommented:
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
 
TheWebGuy38Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TheWebGuy38Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
TheWebGuy38Author Commented:
hrmmmmm

nope that wasn't it. changed @body to @varbody but got the same exact error
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
TheWebGuy38Author Commented:
nope, same thing
0
 
Bhavesh ShahLead AnalysistCommented:
can u post ur new code...
0
 
TheWebGuy38Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
TheWebGuy38Author Commented:
I did, I just changed it back when it didn't work.

let me try your code
0
 
TheWebGuy38Author Commented:
Oh man!!!!!!!!! Your friggen Awesome!!!!

it works great! You are the SQL master! I really appreciate your help
0
 
Bhavesh ShahLead AnalysistCommented:


:-)

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

enjoyy
0
 
Bhavesh ShahLead AnalysistCommented:
Sir,

If you got the answer then Please accept the solution. :-)
0
 
Bhavesh ShahLead AnalysistCommented:

Mr.Webguy

You accpted my wrong post. :-)
Please accpt appropriate post
0
 
TheWebGuy38Author Commented:
can't for the life of me figure out how to get the "accept solution" back
0
 
Bhavesh ShahLead AnalysistCommented:
Hi.
You can reopen Question by clicking Request Attn.Link in top.

Request moderator to reopen the Question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now