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)
Thank this author by sharing:
By Rob Hendershot,
Instead of modiying 100's of lines of code, when migraing SQL2000 to SQL200x and not wanting to use XP_SENDMAIL, use this one-stop-shopping XP_SENDMAIL replacement procedure. Using this procedure reduces the risk of making code change errors in potentially 100's of older procedures, in-line SQL scripts, or applications that still call the SQL 2000 xp_sendmail. To quickly get the benefits of migrating to sp_send_dbmail, and moving away from near-future unsupported calls to the old xp_sendmail, this xp_sendmail replacement perfectly maps old xp_sendmail calls to the new sp_send_dbmail procedure. See code for certain limitations in calling the replacement procedure concerning the scope of the procedure's call. The reason this reduces rick is the fact this one procedure is tried, tuned, and tested. While modifiying tons of old procs, in-line code, and application, with a load of variations in coding methods, style, etc., will certainly lead to the intro of errors, making the small change to call this proc greatly reduces the risk of change. Note there will still be some issues of change in legacy code in dealing with attachment file names, since the use of attached files needs greater security consideration inorder to work under the tighter security regiments found in SQL Server 2005. But for greater majority of calls to xp_sendmail that do not use named attached files, the only change is calling the default location of this new procedure (see the comments section of the code for some details).
Problem sending attachment with XP_SendMail
Which Stored Procedure changed
query results by email using xp_sendmail
XP_sendmail Query variable Parameter