Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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)


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).


Total article views: 1864 | Views in the last 30 days: 2
Related Articles

XP_SendMail attachment error

Problem sending attachment with XP_SendMail


Sendmail either via SSIS or SQL Agent

SSIS Sendmail


Which Stored Procedure changed

Which Stored Procedure changed


query results by email using xp_sendmail

query results by email using xp_sendmail


XP_Sendmail @Query

XP_sendmail Query variable Parameter


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones