How to Use Office365 Email with SQL Server 2000

Introduction

My company migrated from an in house Exchange 2003 email server to a cloud hosted Exchange Online (part of Office 365), and my SQL Server 2000 using xp_sendmail could not connect to it.  This is a selection of stored procedures that replicates the functionality of xp_sendmail, especially the feature of including a query result grid in the email body. 

Although SQL  Server 2000 is beyond support (and in fact we are in the process of migrating to SQL Server 2008 R2), I needed a way to maintain the functionality in the interim.

Background  

My solution uses CDOSYS to send email without Outlook installed.  This has been documented in many places elsewhere, but most of the solutions are missing features. Microsoft's own article doesn't include attachments, cc, bcc, or the ability to include query results. 

• http://support.microsoft.com/kb/312839 
• http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649
• http://www.dbforums.com/microsoft-sql-server/998188-extending-cdosys-mail-include-query-attachments.html

Using the code

In order to use this solution you will need:  

• An Office 365 email account (username, password, etc)
• An IIS server where you can setup an SMTP relay
• SQL Server 2000 (likely to work on others, but only tested on 2000)
• Download CDOSYS_Email.zip 

The code consists of tsql creation scripts for 5 objects:

1. Procedure - GetEmailBodyGrid 
2. Procedure - sp_send_cdosysmail
3. Function - fn_Split  (optional) 
4. Function - GetEmailAddress  (optional)
5. Function - ModifyEmailString (optional)

You will need to go through the script and change a couple of things before you run it: 

• SMTP relay server IP
• Possibly the database into which you create these object (if desired)
• Code to manipulate your email addresses

The real trick here is the procedure GetEmailBodyGrid, which takes as parameters an input query (keep it simple, see limitations below) and returns a formatted HTML table with its results.

The optional objects are all related to email address name resolution.  The problem is that our in house Exchange server would resolve "John Doe" into "jdoe@domain.com" using Active Directory, and I couldn't get that to work here. 

My solution (admittedly clumsy) was to write code to transform the name into the address.  I could have simply changed all the names into email addresses and not worried about it, but that would mean finding all the places where the values are passed from SQL Agent jobs, as well as the many hard-coded instances (I haven't changed all the ones my predecessors set up).  To use my name resolution fix, you will need to have consistent email naming procedures, and work out the logic to go with that.     

To use the procedure, just replace your existing xp_sendmail code like below: 

/*  Old code included for comparison
Exec master..xp_sendmail 
@recipients =  @nvRecipients ,
@subject = @nvSubject , @message = @nvMessage ,     
@query = @nvQuery , @width = @iWidth, @attachments = @nvAttachments
*/

EXEC master..sp_send_cdosysmail
  @From = 'from@domain.com',
  @To = @nvRecipients,
  @Subject = @nvSubject,
  @Body = @nvMessage,
  @query = @nvQuery , 
  @attachments = @nvAttachments

Limitations

• Query complexity - xp_Sendmail could accept pretty much anything as the query parameter.  Here, the query needs to be simple, too many subqueries, unions, complex dynamic sql or multi-steps (store something in temp table, then query off that) seem to choke this up.  To get around that I do something like:  Select into #data from ( [real query] ) as T, then pass Select * from #data into my procedure.  Don't forget to drop the temp table AFTER you send the email. 
• Results Grid Length - SQL Server 2000 only allows varchar(8000), which quickly gets used up when encoding HTML.  This means that you can't use queries with large result sets, they will get cut off.  Since most of my use cases don't have large datasets, this wasn't a problem for me.  You may be able to get around this by using more than one varchar(8000), and returning them all, but that adds a lot of complexity.  See code.