This technique is an oldie but a goodie.
One of Clinton Herring's contributions to "Script of the Day" a while back (I can't remember the date, but the script is dated 08/22/2001) does this too, and, might I add, is a bit more thorough than both examples posted above (Perhaps it gets overlooked because the script title is potentially misleading).
In any event, Clinton's version of this idea uses an OLE automation implementation of the CDOSYS dll, includes error handling, a help/usage message, a clever technique to verify attachments, and, IMHO, is very well-factored.
In addition to any attachments you specify, Clinton's script can also perform a user-defined SQL Query (passed as a parameter to the SPROC), pipe the query results out to a text file (to the drive wih the most free space!), and attach the report to the email! A great idea for automating server reports and emailing them to the DBA, or, generating application-specific reports and emailing them to your business stakeholders.
See what you think!
SQL 2K SMTP mail on Windows 2K
http://www.sqlservercentral.com/scripts/contributions/510.asp
Script Rating
By: Clinton Herring
SP_SQLSMTPMail is an OLE automation implementation of the CDOSYS dll for Windows 2000 which utilizes a network SMTP server rather than an Exchange server/Outlook client. The stored procedure functions similar to xp_sendmail including the ability to run a query and attach the results. No MAPI profile is required. It is also a working, detailed example of an OLE automation implementation. This update corrects a problem when the proc is called twice in the same batch without an intervening 'Go'. The cause is the sp_OAStop. It needs to be removed or commented out. The stated method of operation in the BOL is incorrect. 11/5/2002 Some people have reported errors when running this stored procedure. They have not been failures of the stored procedure. They are errors related to improper configuration/permissions for the SQL server to use the local network SMTP relay server for either internal or out going mail. 11/20/2002 Fixes a problem related to the OSQL call to send an attached query. OSQL was not releasing its lock on the first output file it created until the session ended, hence, calling the proc in a cursor or loop prevented subsequent query attachments. 04/09/2003 Comment correction.
Best,
Ken
