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 |
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 RoussyPlease 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. |
|
|
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 adomain user account as it's startup account.1.1. You can do this a couple of ways. In sql server enterprisemanager, right click on your server instance and go to properties andclick on the security tab. Under Staup Service account you should havea dedicated NT user account. Make sure this NT account's passwordnever expires. Restart your sql service if you change the account.2. You need to create a mail profile for this account on the SQLserver and an exchange account on your mail server. This is simpleenough. If you need directions for this let me know.3. In enterprise manager, expand your server instance and the supportservices folder, right click SQL Mail and go to properties, selectyour profile name and hit the test button.4. Open up the query analyzer and execute xp_startmail. You are goingto want this to start in the event your server or sql servicerestarts. You can do this by using sp_procoption for the xp_startmailprocedure.5. This is where things might get a little vague because I do not haveyour application code or your database to work with but you are goingto want send all of the mail from your VB using the xp_sendmail storedprocedure. This way all of the mail is returned to this account.6. Additionally I would create sql tables to store inbound and outbound emails and in the procedure that sends emails you would writethe outbound emails to a table that includes a customerid and otherdetails as needed.7. Processing inbound emails is a little trickier but the involvedstored procedures should be xp_findnextmsg, xp_readmail, andxp_deletemail and you would use these procedures to process all of themail in your centralized mailbox and write the returned results toyour table that holds all of your inbound email data.8. Then you use these tables that hold the inbound and outbound emailin 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 athird party plugin called xp_smtpmail.Sean RoussyPlease 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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 |
|
|
|
|
|
|
|