And look ma, no Outlook!!! I had been writing a whole bunch of reports recently, essentially just a bunch of queries and having them e-mailed out to me every night. To accomplish this I was using the ColdFusion and it’s scheduling capabilities, however in the recesses of my mind I remembered something about using MS SQL server to that job, which in this case made a hell of a lot of sense. So off I went in search of this vague memory and a way to capture and apply it.

Sure enough a simple search on Google reveals plenty of information about using MS SQL to send e-mail. This is a good starting point. However, it wasn’t long before the first hurdle was thrown my way. Whereas previous versions of MS SQL quite happily supported sending e-mails, MS SQL 2000 actually requires a copy of MS Outlook to send messages (why? Have a read here). And what did we use? MS SQL 2000. Of course being tight, there was no way that the company was going to splash out on another copy of MS Outlook 2000, so back to square one.

Back to Google, another search this time a solution please without using Outlook. The solution presented itself on the ever excellent Experts-Exchange.

The trick, well go to this site here. Here you’ll find detailed instructions and the relevant files to download. A 54k download later, I had copied the relevant file [for 2000 use this one: xpsmtp80.dll] to the ’<your drive letter>:\Program Files\Microsoft SQL Server\Binn’ folder and opened up my query analyzer.

In order to use this DLL and the stored procedures associated it with it, you’ll need to first register it and then assign it the right execute permissions. Below are the steps required as found on the aforementioned web site.

– SQL Server 2000 install
exec sp_addextendedproc ‘xp_smtp_sendmail’, ‘xpsmtp80.dll’
– give execute permissions
grant execute on xp_smtp_sendmail to public

Well nearly ready to go if all this worked, however I recommend you change the execute permissions to a user more suited to this task. So here is how I tested that it was all working by running the following code through the query analyzer again:

– ************************************************************************
– greg’s minimum number of parameters
– ************************************************************************
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N’from@yourdomain.com’,
@TO = N’to@yourdomain.com’,
@subject = N’Hello SQL Server SMTP Mail’,
@message = N’Goodbye MAPI, goodbye Outlook’,
@server = N’mail.yourdomain.com’
select RC = @rc
go

Easy enough. As indicated have a good read through the site where this information originally came from as it contains a lot of information on the different options at your disposal.