Diary of a Techie

Microsoft SQL 2000 driven websites
Microsoft SQL 2000 is a database server software. Increasingly, a number of web developers require us to host their application with Microsoft SQL 2000 as their back end database in our bnshosting.net service.

Why? Well, a lot of things can be done when your web application has a database back end. You can store information and retrieve this at a later date in a web format. So your users can access your data from a web browser. You can store details of web transactions. You can use it to match a ‘supplier’ with a ‘consumer’.

 

Take the case of dating services or job hunting websites like Trabaho.com. When you register as a job seeker, the web application will ask a few details of you. Your name, age, educational background , job experience, and your ideal job. It also asks the employer similar questions.  The web application will store these information in SQL database, and using a few simple ‘select’ queries, can match the job seeker with the right employer.

SQL Mail Functionality
Microsoft SQL also comes with SQL Mail functionality. If setup properly, the sql server system can send an email to a recipient of your choice whenever a ‘trigger’ event is activated.

For example, suppose you are running a web based accounting system, and you want to be notified whenever a accountant makes a ‘void’ entry or deletes a record. SQL server can use the sql mail capability to fire up the email whenever such an event happens.

In the case at hand, my client uses the SQL mail functionality to notify his sysads that certain house keeping jobs are done successfully. When a backup job or data transformation job completes successfully, he receives an email. When the job in question fails, for one reason or another, he can opt to receive an email as well.

SQL Mail Fails
My client’s sql and sql mail has been running without problems…until a few months ago. On the surface, you would not suspect something was afoot. There were no error messages, and all test came back successful. I took a look at the normal causes of SQL mail failures.

I made sure that the sql agent service was running. It was using a domain user account. I also made sure that there was a mail profile (control panel/mail). I went into the SQL enterprise management console and made test on BOTH the sql server and the sql agent .

I also made sure the that SQL Mail in the support tools was running.

All test came back successful. When you hit the test send button, they will say that the “Message was sent successfully” or that the “mail profile was used to stop and start the service”.  I also verfiied that the recipient email addresses could receive emails from other domains. So that wasn’t it. The only problem was, the recipients were STILL NOT getting any emails.

The key thing to remember was “The system USED to work”

SQL MAIL Clues
Since Microsoft Outlook 2002 was installed, I decided to launch it and try to see if we could send and receive emails using the mail profile that was set to be used by the SQL server. I could not get it to open the profile. Apprantly, this was because SQL was using the same profile.

So I went over to SQL Enterprise manager and changed the profile in the system to use another profile. Then, I went back to Outlook and log in. This time I was able to log in and using the profile. Immediately, I saw what was wrong.

I saw OUTLOOK sending all the test mails that I fired a while back! It seems that all these emails went to the OUTBOX and were stuck there. It was not being sent out.

So I did a little background research (google “sql mail stuck outlook”)  and found something new that I hadn’t figured.

http://support.microsoft.com/default.aspx/kb/315886

“Why did SQL Mail stop working after I upgraded Microsoft Outlook or applied the Outlook security patch?

A2: An Outlook 2000 E-Mail Security Update released in August 2001 prevents the spread of e-mail worms that propagate by sending themselves to everyone in your address book. The security patch detects when a non-Outlook program sends an e-mail and a dialog box with this message appears:
A program is trying to automatically send e-mail on your behalf. Do you want to allow this?
You must click Yes if you want to send the mail.”
 

http://support.microsoft.com/default.aspx/kb/315886

“How can I prevent SQL Mail from hanging if I have the Outlook security patch or I’m using Outlook 2002?

A4: You must work with your Exchange administrator to change the default security settings. The exact procedure depends on whether you are using Outlook 2000 or Outlook 2002 (provided with Office XP). For additional information about changing the Exchange security settings, click the article numbers below to view the articles in the Microsoft Knowledge Base:
263297 (http://support.microsoft.com/kb/263297/EN-US/) OL2000: Administrator Information About the Outlook E-mail Security Update
290499 (http://support.microsoft.com/kb/290499/EN-US/) OL2002: Administrator Information About E-Mail Security Features
Q5: Why is my outgoing mail stuck in the Inbox?

A5: This problem is more likely to occur with Outlook 2002. To improve Internet e-mail performance, the Office development team moved the mail spooler for non-Exchange mail into the Outlook process space. You must have the Outlook client open to send outgoing e-mail. This problem does not affect the e-mail that Outlooks sends to the Exchange server.

You may also encounter this problem when you use Outlook 2000. In this case, the most likely cause is that you did not provide a working POP3 server as the incoming mail server in the mail profile.”

resources:

http://www.mssqlcity.com/Articles/Adm/mail_troubleshooting.htm

SQL MAIL solution
It turns out, that my client uses an ordinary POP3 email account as the profile being used by the SQL server. While this is a workable solution, it needed the outlook client to beup and running for the emails to be sent out.

The solution we arrived at was simple. Since my client also had an exchange server, I created an account on the exchange server, and created a profile in the SQL server to use this exchange based email account. Once the profile was created, I then set the SQL server to use this profile for sending out emails.

SQL mail is again working. (Article also published in INQ7.net)

Filed under: Computing