Link to home
Start Free TrialLog in
Avatar of TheWebGuy38
TheWebGuy38

asked on

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

Avatar of subhashpunia
subhashpunia
Flag of India image

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/
Avatar of TheWebGuy38
TheWebGuy38

ASKER

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

Avatar of Bhavesh Shah
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

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?
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
hrmmmmm

nope that wasn't it. changed @body to @varbody but got the same exact error
try this....

EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @Subject,
    @body = 'Body',
    @body_format = 'HTML',
@profile_name='Reef Junkies';
nope, same thing
can u post ur new code...
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

ASKER CERTIFIED SOLUTION
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I did, I just changed it back when it didn't work.

let me try your code
Oh man!!!!!!!!! Your friggen Awesome!!!!

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


:-)

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

enjoyy
Sir,

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

Mr.Webguy

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

Request moderator to reopen the Question.