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
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
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? :)
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;
Check out this.....
your cursor declaration was not proper.
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;
ASKER
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?
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
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
ASKER
hrmmmmm
nope that wasn't it. changed @body to @varbody but got the same exact error
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';
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
@from_address = @FromAddress,
@subject = @Subject,
@body = 'Body',
@body_format = 'HTML',
@profile_name='Reef Junkies';
ASKER
nope, same thing
can u post ur new code...
ASKER
Sure
here it is
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did, I just changed it back when it didn't work.
let me try your code
let me try your code
ASKER
Oh man!!!!!!!!! Your friggen Awesome!!!!
it works great! You are the SQL master! I really appreciate your help
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. :-)
If you got the answer then Please accept the solution. :-)
Mr.Webguy
You accpted my wrong post. :-)
Please accpt appropriate post
ASKER
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.
You can reopen Question by clicking Request Attn.Link in top.
Request moderator to reopen the Question.
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/