SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL 2K SMTP mail on Windows 2K

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. 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: 3721 | Views in the last 30 days: 10
Related Articles

Stored procedure slower then query

Stored procedure slower then query


Stored Procedure vs Parameterized queries

Stored Procedure vs Parameterized queries which one is the best


Query Performance Issue

Query attached


Temporary Stored Procedures

Temporary Stored Procedures - little known sql server feature


Creating Stored Procedures - SQL School Video

MVP Brian Knight covers the basics of creating stored procedures in SQL Server. Stored Procedures ar...