Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-17 : 17:56:02
|
Ben writes "SQL Server 7, SP 2
Windows NT Server 4, SP 6a
Outlook 2000
Hello,
SQL Team always teaches me something everytime I visit, and I found the article "SQL Mail In Depth" to be most interesting.
I have written the following SQL batch that rolls through an Outlook Inbox, write the e-mails to a table, and then deletes each e-mail after insertion.
The problem: It moves through all the e-mails and inserts them all into the table, but it only deletes the first e-mail and leaves the rest in the Outlook Inbox. The next time the batch is run, they are written as duplicates into the table, which is hardly the desired result.
Here is the batch, which is largely "inspired" by the code samples in the "SQL Mail In Depth" article:
/* SQL Script write_delete_mail.sql */
/* Script objective: */
/* 1) Move through all items in Outlook 2000 Inbox. */
/* 2) Insert each item to tblMail in database Mail_Test.*/
/* 3) Delete each item after above insertion. */
DECLARE @hMessage varchar(255), @MessageDate varchar(255), @SenderAddress varchar(255), @MessageSubject varchar(255), @MessageText varchar(1000),
@MessageAttachments varchar(1000)
/* Find the first e-mail in the Inbox. */
EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
/* Write to output parameters so values can be used for insertion into table and deletion from Inbox. */
EXEC xp_readmail @msg_id=@hMessage, @date_received=@MessageDate OUT, @originator_address=@SenderAddress OUT,
@subject=@MessageSubject OUT, @message=@MessageText OUT, @attachments=@MessageAttachments OUT
/* Insert values into tblMail in Mail_Test database. */
INSERT INTO Mail_Test.dbo.tblMail (MsgID, Date_Received, Sender_Address, Message_Subject, Message_Text, Message_Attachments)
VALUES (@hMessage, @MessageDate, @SenderAddress, @MessageSubject, @MessageText, @MessageAttachments)
/* The next line only deletes the only the first e-mail in the Inbox even though all e-mails are successfully inserted. */
/* The objective is to delete each e-mail from the Inbox after it is inserted.*/
EXEC xp_deletemail @hMessage
/* Find the next e-mail in the Inbox. */
EXEC xp_findnextmsg @msg_id=@hMessage OUT
END
Am I missing something painfully obvious, or can this even be done? Your input is appreciated.
Thanks,
Ben" |
|
|
|
|
|
|
|
SQLMail Batch Script: Insertion and Deletion