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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 using SQL server to receive email

Author  Topic 

eds
Starting Member

3 Posts

Posted - 2005-06-15 : 10:27:14
Hi,

could someone point me in the right direction, or even develop it for me and I will pay for it. (if the price is agreeable).

I have a customer SQL DB with a VB front end. From the front end we are able to send emails but are forced to use exchange to receive emails. The problem is if one of the customer service reps are out we can only view notes and emails sent from VB front end.

I dont understand how to connect SQL server2k to exchange, if an email was received from jsmith@test.com and the custmer had that email address register in their recordset, it would be great if It was registered in the customer recordset hence when we opened jsmith we could see the emails received double click it and access the email.

Eventually I would like to do away with exchange and only use our in house db. We are running exchange 5.5 sql server 2000. e@edesousa.com

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-15 : 10:49:14
I am at a slow point in the office and I could use a project. What version of VB?

I am not sure if getting rid of exchange is a good idea because the alternative is SMTP and all of the bugaboos that come with that.

You can connect sql server to exchange using sql mail and using it's associated extended stored procedured. SQL Server has to be using a domain account and there is a little set up to do here.

Let me see if I get what you are trying to do, when an email comes in from a customer, you want to be able to open his customer record and see the emails he has sent. Simple enough. Email me if you want me to do this and we can talk about it.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-17 : 11:50:21
1. First, you need to make sure that the SQL Server account is using a
domain user account as it's startup account.

1.1. You can do this a couple of ways. In sql server enterprise
manager, right click on your server instance and go to properties and
click on the security tab. Under Staup Service account you should have
a dedicated NT user account. Make sure this NT account's password
never expires. Restart your sql service if you change the account.

2. You need to create a mail profile for this account on the SQL
server and an exchange account on your mail server. This is simple
enough. If you need directions for this let me know.

3. In enterprise manager, expand your server instance and the support
services folder, right click SQL Mail and go to properties, select
your profile name and hit the test button.

4. Open up the query analyzer and execute xp_startmail. You are going
to want this to start in the event your server or sql service
restarts. You can do this by using sp_procoption for the xp_startmail
procedure.

5. This is where things might get a little vague because I do not have
your application code or your database to work with but you are going
to want send all of the mail from your VB using the xp_sendmail stored
procedure. This way all of the mail is returned to this account.

6. Additionally I would create sql tables to store inbound and out
bound emails and in the procedure that sends emails you would write
the outbound emails to a table that includes a customerid and other
details as needed.

7. Processing inbound emails is a little trickier but the involved
stored procedures should be xp_findnextmsg, xp_readmail, and
xp_deletemail and you would use these procedures to process all of the
mail in your centralized mailbox and write the returned results to
your table that holds all of your inbound email data.

8. Then you use these tables that hold the inbound and outbound email
in your Customer VB screen to view the correspondance.

Let me know if you have any questions.

Other alternatives include using CDOSYS from within the VB code, or a
third party plugin called xp_smtpmail.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-17 : 12:05:36
Well, it's hard to get paid when you just give them the answers...





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

eds
Starting Member

3 Posts

Posted - 2005-06-20 : 10:46:23
Hi found the following solution trying to decide which one to implement?

Forward all messages on to a pop account. This pop account held on a mail server service that uses SQL as its message store.

I've just been scouting around for something that will do this http://www.floatingbear.ca/botdocs/mailbot.htm
Go to Top of Page
   

- Advertisement -