Search

Wednesday 29 September 2010

[SQL] SP_SEND_DBMAIL

Below is the syntax to generate an smtp mail from within SQL SERVER.

-- Used on SQL SERVER 2005/2008
DECLARE
@vRecipients VARCHAR(MAX),
@vProfile VARCHAR(20),
@vSubject VARCHAR(MAX),
@vBody VARCHAR(MAX),
@vSQL varchar(MAX)

SET @vRecipients = 'AnyOld@Email.com'
SET @vSubject = 'Email Subject'
SET @vProfile = 'default'
SET @vBody = 'Body of email'
SET @vBody = @vBody+CHAR(13) --Char(13) is a CARRIDGE RETURN
SET @vSQL = 'select ''Type in your SQL statement here'''

EXEC msdb.dbo.sp_send_dbmail
@recipients= @vRecipients ,
@subject=@vSubject,
@body=@vBody,
@profile_name = @vProfile,
@query = @vSQL