Pop3 Client Service Engine instead of SQLMail

• Download Installer only - 224.59 KB
• Download solution (entire source code) - 604.02 KB

The Prelude

I'm responsible for administering Microsoft SQL Server in our company. Some parts of our system were unstable, i.e., every morning I began with fixing SQLMail (+Outlook) because messaging between services and part of workflow were based on this service. I think many of you experience difficulties with this service. Here are errors which I was getting:

• xp_readmail: Failed with operating system error 32 [SQLSTATE 42000]
• xp_findnextmsg: Failed with mail error 0x80040108 [SQLSTATE 42000])

Obviously, for majority of users ???? e-mail sending is enough, but for me it wasn't so, using XPSMTP didn't solve all problems. I was seriously in need of a simple POP3 client. After long and unsuccessful searches for a suitable application, I came to writing this piece of software by myself. So this work is published here with all sources included.

Why Is It Open Source?

I don't think that I've written something outstanding and complete. And if someone will find a bug or a piece of code that can be improved (every software contains lots of them) and will send me a diff with changes, I would be very thankful to that person. Version control is handled by TortoiseCVS.

Why C#.NET was Used for Development?

If you want my opinion, it is convenient for programming under Windows and Windows Server, especially for server applications. You will need Microsoft .NET Framework 1.1. or you can work with POP3 Client Service. The prototype of set of classes for work with POP3 was taken from The Code Project, A POP3 Client in C# .NET. But, after coming across lack of some features, I've significantly rewritten it.

Installation Process

1. Installing service (start Setup.Exe and finish by pressing the "Finish" button)
2. Find %WINDOWS%/system32/BC_POP3Client.xml, and edit it for your needs, basically required only to edit
• Account – which you will use to fetch mail
• sqlConnection – where you want it stored
• AttachFolder – where attachments will be stored
3. Install DBObjectCreate.SQL (where sqlConnection is pointing)
4. Don't forget to setup service in Start>Settings>Control Panel>Administration>Services>BC_POP3Client>Login (by default, it is started under system account and doesn't have access to the network)
5. There, you can also start it. That is all. Enjoy.

Now About Work of Service

For you as the end user, the most important tables are mail and mail_attchment: they store information about messages.

Stored procedures were written to be similar to SQLMail.

mail_findnextmsg 
@msg_id INT, output gives id of unread message ([mail].[isRead] = false) 

mail_readmail 
@msg_id INT - which message to read 
@attachments VARCHAR (1024), output – list of attachments 
@originator_address VARCHAR (1024), output – senders address 
@originator VARCHAR (1024), output – senders name 
@date_received datetime, output – received date 
@subject VARCHAR (1024), output – message subject 
@recipients VARCHAR (1024), output - recipients 
@message VARCHAR (8000), output – message body 

The set of parameters match procedures of SQLMail because I wanted to make implementing my solution straightforward - make procedure dump, change calls and everything works.

P.S.: Feel free to contact me if some problems arise. We will try to solve them together.

History

• 11^th November, 2005: Initial post