Add email contents to table using xp_readmail

[lmorley]

Thanks in advance

I need to create new records based on emails received in
SQL Mail. My server is set up for mail and operating fine.
I have only needed to send mail so far.

I've only found how to read mail and respond to it,
but not how to write data to a table from contents.

I need to process mail received where the subject is only
subject:mydata
Deleted all other mail received.
Then write to table1 each mail message from the body
Body: data1,data2,data3,data4.data5

Thanks Again,

http://WWW.LAM2.COM

 

[tlbroadbent]

Here is a sample of T-SQL code that reads a message and inserts data into a table. This procedure requires that the data to be inserted be sent in the message body as a comma delimited string. The character and datetime type data must be enclosed in single quotes as in the following example.

'Testing 1',25,'Jul 12 2001'

----------------------------------

SET nocount ON
USE master

DECLARE @originator varchar(255), @subj varchar(255)
DECLARE @msg varchar(255), @sql nvarchar(2000)
DECLARE @status int, @msgid varchar(255)

SET @status = 0
SET @msgid = ''
SET @msg=''

EXEC @status = master.dbo.xp_findnextmsg @msg_id = @msgid OUTPUT
IF @msgid IS NULL RETURN

EXEC @status = master.dbo.xp_readmail @msg_id = @msgid,
@originator = @originator OUTPUT,
@subject = @subj OUTPUT,
@message = @msg OUTPUT,
@peek = 'FALSE',
@suppress_attach = 'TRUE'

IF @subj='mydata'
BEGIN
SET @sql='Insert utilitydb.dbo.test2 (Col1, Col2, Col3) Values (' + @msg + ')'
PRINT @sql
EXEC sp_executesql @sql
END

EXEC xp_deletemail @msgid

----------------------------------

NOTES:

The maximum message size in xp_readmail is 255 characters. You need to loop through the message if the messages will be longer than 255 characters. See SQL BOL for an example of how to read longer messages.

The @peek option indicates whether xp_readmail should mark the mail as read or not. You will want to set this option to false if you have other processes that read mail.

Deleting the messages will prevent any other processing from reading them. You may want to modify the process to only delete mail with the subject 'mydata.'
Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[lmorley]

Thanks Terry L. Broadbent
This code doen't put data into my tables.
It does read the messages and deletes them.
There are no errors that I can find.
What's missing?

SET @sql='Insert My_DB.dbo.Table1 (Col1, Col2) Values (' + @msg + ')'

Does the Col1, and Col2 have to be the name of the actual ones in the table?

Also does the sub in the email need 'mydata' or mydata.

Thanks LAM2

 

[tlbroadbent]

1) col1 and col2 should be changed to the actual names in your table.

2) Leave the quotes off the subject in the email message.

3) Did you include the EXEC line in your procedure?

EXEC sp_executesql @sql Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[lmorley]

Terry,

It works for one email fine now.
How do I get it to loop through all email and only process
the matching subject. (I tryed but I'm lost now)

 

[tlbroadbent]

Add a WHILE LOOP to the procedure.

WHILE (1 = 1)
BEGIN

EXEC @status = master.dbo.xp_findnextmsg @msg_id = @msgid Output

.
.
.

EXEC xp_deletemail @msgid
END

Also change
IF @msgid IS NULL RETURN
to
IF @msgid IS NULL BREAK Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[lmorley]

Great! I've got it working now!
Thanks for your Help and Direction! :->