SQL Mail keeps hanging - meaning I have to restart SQL Server to clear it.

A. This is a mapi problem, not a SQL one, but having SQL Server acting as a MAPI client makes it nearly impossible to track down and fix. SQL 6.5 SP5a has a fix in to reduce these problems. I have seen at least one report of SQL 7.0 having the same problem - so it doesn't look like this handles it a whole lot better.

You can try a different mail client and see if the mapi dll's that installs work better - some people have found that Outlook 98 works well, others report that only very early versions of Exchange client work ok. Whatever the case it is likely that bugs exist in all versions of the mapi client and are exposed on fast or SMP machines due to race conditions - thus causing hangs.

The recommended workaround would be to not use SQL Mail at all. You can use a SENDMAIL.EXE command - either the one in the Exchange resource kit for MAPI, or an SMTP one for an internet gateway. This can then be called in two ways :-

1. If you want mail to go synchronously then call SENDMAIL via xp_cmdshell. This then won't return control to your SQL task until the mail has been sent.

2. If asynchronous sending of mail is ok, or you are worried about the mail process hanging individual threads then write out the mail message information to a flat file. Then have a looping batch process outside of SQL looking for emails and sending them when it sees them. This process could update SQL tables to say they are sent if that is needed.

I prefer the second approach.