receive email into database 1

[mici]

I am trying to transfer information from exchange server to a sql database.
Every time when a new email arrives to a specific address i want all the details to be transfered to a sql server db.
Is there a built-in dll or rule ? can i write something using ASP to create this situation?
where do i start ? please help!
Thanks

 

[SQLSister]

forum955 might be a better place to get help with this.

Questions about posting. See faq183-874

 

[mrdenny]

Mici,
Look at xp_readmail in BOL. It is the built in procedure for having SQL Server read mail.

Denny

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[ashishsmith]

hey i have done the same thing. I had some thing similiar and played into my limitations. What i did is this:

I save the email to a specific name on the pc and run a c++ code, which should be accessing that file and creates another output file with the delimited fields, delimeter being "|" and after that just simply run the DTS package to get input from the output from text file and transfer it to the sql server database.

You may write a code in whatever language, being it should be an exe coz you don't want to compile each time you want to run the code.

Ashish Smith

 

[mici]

Thank you for the answers , I do have one question:
following some of your advice, i was thinking to forward all the mail of a specific account from the exchange server(5.5) to sql mail and then read it from the sql mail in to the database of sql (2000) in the order i want using the xp_readmail you suggested.
1.Does it sound possible?
2.How is the sql mail work? how all the information is being kept? is it one file, or is it organized by fields?Thank you.

 

[mrdenny]

1. Yes.
2. Here is the basic setup info.
Create a mailbox on your Exchange Server (5.5 will work fine) for your SQL Server. It doesn't really matter what it's called unless you are going to be sending mail from it to people outside the company. When setting up the mailbox, if you have 5 different email addresses that you want the SQL Server to read the mail from, you can just enter them all in the email addresses tab and the mailbox will collect all mail for those 5 addresses that it receives into that one box. So no need to setup forwards from one box to another.

The log into the SQL Server with the NT Account that the SQL Server is running as, and configure outlook to use that account. Then setup the SQL Server to use Outlook for sqlmail. There are several FAQs on Tek-Tips that cover doing this. faq962-4452 is the one I wrote. faq183-3146 is another one by tlbroadbent (I'm partial to mine).

When you use SQL Mail to read the mail you call the procedure and tell it what message you want to read (I'll explain more below), and the procedure returns the values from the message. You can then do with them as you please. You can store them in a table, or use the data that you received to run another procedure. The emails are stored on the Exchange server until xp_readmail reads the message. There is another procedure called xp_deletemail which is used to delete the message. You would normally run these in a loop to read all the messages.

Now keep in mind that this does not happen by it self, you have to do this manually, or setup a job to process the incomming mail every x minutes. I usually set it up to process every 5-10 minutes depending on how fast I need the messages put into the system. If people will be emailing the system, and expecting a responce then you might want it to check more often.

There is a stored procedure in the master database called sp_processmail which I normaly use as a template when setting up a SQL Server to read mail. It's got the loop already setup, and all the catching that you need to do for when you have read all the messages. You can edit the procedure and save it as a new procedure with a new name with your custom parts and then run the procedure to read the mail.

Read up on xp_readmail in BOL. It's got a list of all the paramaters that it can pull from.

If you decide that you want to send a lot mail google xp_smpt_sendmail, it is much better than the xp_sendmail that comes with SQL Server, as it doesn't use outlook to send. It uses what ever SMTP server you specify, and you can send from any email address.

If you need more info, just let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mici]

Thank you so much, i feel it is the right direction.
However i need abit more help:
i read all you wrote, include the "How do I setup my SQL Server to use outlook to send and receive mail?"

and i am not sure do i have to choose between sqlmail and sqlagent?
if yes- does the agent operates by itself as long as outlook is open?(no need fr procedures?)
can the outlook client be outlook express?
I tried doing the first step of configuring the profile under "mail" , and then the operator.
when i try to configure the profile the combo box is empty, and i am thinking maybe the following is my problem:
in the msdn:"If you want to use a separate e-mail account for SQLAgentMail, log on to Windows with the account that the SQLSERVERAGENT service uses, and create a mail profile for that account to use as well. "-is it?
can u help again?
Thank you.

 

[snowboardr]

Mr Denny is there a way to do this with MySQL database?

www.sitesd.com
ASP WEB DEVELOPMENT

 

[mrdenny]

mici,
I'll try to answer each question seperatly.

No you do not need to choose between sqlmail and sqlagent. sqlmail is part of the SQL Server and SQLAgent is a seperate service running on the system. SQLAgent actually has it's own mail subsystem called SQLAgentMail (You really don't need to remember this, but background is always good). You use SQLMail and SQLAgent (SQLAgentMail) at the same time, the system is designed for this.

The mail client needs to be a MAPI client, so Outlook Express won't work for you. It needs to be some version of Outlook (Outlook 97 or greater is recommended).

There is no need to leave Outlook open. SQL will start outlook as needed to send and receive mail. (This changes is you are using a POP3 and SMTP server instead of an Exchange server. Using a POP3 and SMTP server is much tricker as this was intended to be done using Exchange.)

When setting up the mail be sure to log into the server as the same account that the SQL Server is running under (and be sure that the SQL Server is really running under that account). Then you will probably need to stop and restart the SQL Service to get it to put the profile name in the drop down box.

If you want to use a separate e-mail account for SQLAgentMail, log on to Windows with the account that the SQLSERVERAGENT service uses, and create a mail profile for that account to use as well.

What they are talking about here is if you want mail from xp_sendmail and the like to come from one address, and alerts and messages sent to operators sent from another address. In order to do this you have to configure the SQL Agent as follows. Create a second account on your domain and configure the SQLSERVERAGENT service to run under that account. Then log in with that second account and setup the mail profile, stop and restart the agent and configure the agent. I have never had the desire to do this. I don't really see a benefit to doing it.

snowboardr,
As MySQL is open source I'm sure that there is a way to do it. It would probably have to be written into the code of the database engine. As MySQL doesn't support Extended Stored Procedures it can't be easily extended with this kind of support with out major changes to the database engine it self. Also as far as I know MySQL does not have an equilivent to the SQL Server Agent.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mici]

Thanks again mrdenny,
I have made some progress with the procedures ,
I have tried the following code which sometimes works and show results of all my emails in the inbox BUT sometimes i get this message:xp_readmail: "failed with mail error 0x8004010f"
I have read that the problem might be solved by installing service pack 2. I installed sp 3 but i still get this message .(right after reboot i get the results but then the second time and on i get this message).
what do u think?

declare @msg_id varchar(64)
declare @status varchar(64)
declare @message varchar(64)
declare @skip_bytes int
declare @msg_length int


USE master
WHILE (1 = 1)
BEGIN
EXEC @status = xp_readmail @msg_id = @msg_id,
@message = @message OUTPUT,
@skip_bytes = @skip_bytes OUTPUT,
@msg_length = @msg_length OUTPUT

IF @status <> 0 BREAK
SELECT 'msg_id' = @msg_id, 'msg_part' = @message
IF @skip_bytes = @msg_length BREAK
END

another thing-i am not going to read the messages in the inbox, but only after i insert them to the sql db. So if i want to use the read procedure only for the new messages that i havent insert to the db yet, what is the best way to do it? (if i use "unread", will it read the unread messages and then mark it as read for the next time?)

 

[mici]

hi mrdenny
could you read my last post?