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.
If anyone has any code that's similar, I'd greatly appreciate it
8 6379
OK, this is a mess, but it gives me something to work with. any help would be appreciated. I'm not very good with stored procedures -
-
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
-
-
-
-
-
-
-
-
-
-
errr update.
I know I'm close, but not working yet.
also screwed up the curser which I don't really understand.
updated 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 @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;
-
-
-
-
-
-
-
-
ok,, working 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;
-
-
-
Your code will work fine.
You may implement this without a cursor, just a WHILE LOOP. Also, you have to check for errors before you update the HasSent to 1. What if the Mail failed? Your table will say the email has been sent already even if it's not true.
Happy Coding!!!
~~ CK
What would the code look like to check for errors?
Here, this is a good read.
Good Luck!!!
~~ CK
Something like this? -
-
Try
-
-
-
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)
-
-
Catch e as Exception
-
-
End Try
-
-
Close enough. Here's the full syntax of the TRY..CATCH command.
Good Luck!!!
~~CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nashat Wanly |
last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft...
|
by: Abdulla Herzallah |
last post by:
Hi every one I have tried to search for any links, hints clues on how to
create a Stored Procedure programmatically using C# but came back with empty
handed :-(
I know it is definitly possible...
|
by: harborboy76 |
last post by:
Hi,
I have a stored procedure that does a lot of INSERT/UDATE to 3
tables. And When I call the stored procedure, I get a Transaction Log
Full error. When I want to do is turning off the...
|
by: usenet |
last post by:
Hi.
I have a DB2 stored procedure that I call using JDBC. Now I'm trying to
batch the call. This is done by the book:
stmt = connection.prepareCall(" ... ");
stmt.setString(1, "foo");...
|
by: sowmyavenkat |
last post by:
hi all,
Iam working on a Maintainance and enhancement project. It was developed by somebody. Now there is a bug which is to be solved. Can any body help me to find out which stored procedure is...
|
by: ambikadevi |
last post by:
What is the function to call the Postgres stored procedures function using ADODB? PrepareSP() function is used to call the Oracle Stored Procedure. For PHP application.
Waiting for the response!
|
by: ckauvar |
last post by:
The PHP below calls a stored procedure in a MSSQL database when I am
using SQL in a Windows environment. I've recently switched to a UNIX
environment and am now using ODBC (via FreeTDS) to connect...
|
by: Dooza |
last post by:
I have a stored procedure that needs to be executed via an ASP page.
The SP does a number of tasks, mainly comparing a local table against a
linked server table, and updating/inserting/deleting...
|
by: gjain12 |
last post by:
When I am trying to declare a cursor over a temproay table in my stored procedure I am getting some errors.
Below are the piece of code from my stored procedure in which I am getting error.
...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |