SQL 2K SMTP mail on Windows 2K

Total article views: 1834 | Views in the last 30 days: 120

 Rate this |   Join the discussion |  Briefcase

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. Not written to mimic xp_sendmail exactly. 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. 11/15/2004 For the query attachment feature to work the SQL startup account must have rights to the C Drive and osql must be available. If you wish to use this script with NT 4.0 then copy over and register the cdosys.dll version 6.1.3940.29 dated 7/22/2002 11:05 am. Use at your on risk. 9/12/2005 For this to work properly with Windows 2003 & the new secuirty model one must include a value for @vcSenderName of the format @.com such as @.com, ie, TOX1SQLQT1@Perry.com.

Total article views: 1834 | Views in the last 30 days: 120