Rick Caminiti dotCOM» Sending Mail with MSSQL

Home > BI, Code, Tips > Sending Mail with MSSQL

Sending Mail with MSSQL

May 15th, 2009 NothingMan Leave a comment Go to comments

Here’s a quick and dirty way to send email via MSSQL. For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option. Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell. Fun stuff.

declare @developerperson varchar(80)

declare @message_body varchar(1000)

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ‘’s’

EXECUTE dbo.sp_SQLSMTPMail

@vcTo = ‘You@you.com’,

@vcCC= ‘Them@them.com’,

@vcSubject = ‘Super Cool SQL Email’,

@vcBody = @message_body,

@vcFrom = ‘Me@me.com’,

@vcSMTPServer = ‘SMTPName’,

@vcSenderName = ‘Some One’,

@vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

@vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ’select col1, col2, col3, col4 from database.schema.table’

Here’s the actual Proc definition:

Create Procedure dbo.sp_SQLSMTPMail

@vcFrom varchar(128) = null,

@vcTo varchar(2048) = null,

@vcSubject varchar(255) = null,

@vcBody varchar(8000) = ”,

@vcCC varchar(2048) = ”,

@vcBCC varchar(2048) = ”,

@vcAttachments varchar(1024) = null,

@vcQuery varchar(8000) = null,

@vcSMTPServer varchar(255) = ‘192.168.1.55′, – PUT YOUR LOCAL SMTP SERVER HERE

@cSendUsing char(1) = ‘2′,

@vcPort varchar(3) = ‘25′,

@cAuthenticate char(1) = ‘0′,

@vcDSNOptions varchar(2) = ‘0′,

@vcTimeout varchar(2) = ‘30′,

@vcSenderName varchar(128) = null,

@vcServerName sysname = null

Set nocount on

– Determine if the user requested syntax.

If @vcTo = ‘?’

Begin

Print ‘Syntax for sp_SQLSMTPMail (based on CDOSYS):’

Print ‘Exec master.dbo.sp_SQLSMTPMail’

Print ‘ @vcTo (varchar(2048)) – Recipient e-mail address list separating each with a ”;” ‘

Print ‘ or a ”,”. Use a ”?” to return the syntax.’

Print ‘ @vcBody (varchar(8000)) – Text body; use embedded char(13) + char(10)’

Print ‘ for carriage returns. The default is nothing’

Print ‘ @vcSubject (varchar(255))) – E-mail subject. The default is a message from’

Print ‘ @@servername.’

Print ‘ @vcAttachments (varchar(1024)) – Attachment list separating each with a ”;”.’

Print ‘ The default is no attachments.’

Print ‘ @vcQuery (varchar(8000)) – In-line query or a query file path; do not ‘

Print ‘ use double quotes within the query.’

Print ‘ @vcFrom (varchar(128)) - Sender list defaulted to @@ServerName.’

Print ‘ @vcCC (varchar(2048)) – CC list separating each with a ”;” or a ”,”’

Print ‘ The default is no CC addresses.’

Print ‘ @vcBCC (varchar(2048)) – Blind CC list separating each with a ”;” or a ”,”’

Print ‘ The default is no BCC addresses.’

Print ‘ @vcSMTPServer (varchar(255)) - Network smtp server defaulted to your companies network’

Print ‘ smtp server. Set this in the stored proc code.’

Print ‘ @cSendUsing (char(1)) - Specifies the smpt server method, local or network. The’

Print ‘ default is network, a value of ”2”.’

Print ‘ @vcPort (varchar(3)) - The smtp server communication port defaulted to ”25”.’

Print ‘ @cAuthenticate (char(1)) - The smtp server authentication method defaulted to ‘

Print ‘ anonymous, a value of ”0”.’

Print ‘ @vcDSNOptions (varchar(2)) - The smtp server delivery status defaulted to none,’

Print ‘ a value of ”0”.’

Print ‘ @vcTimeout (varchar(2)) - The smtp server connection timeout defaulted to 30 seconds.’

Print ‘ @vcSenderName (varchar(128)) - Primary sender name defaulted to @@ServerName.’

Print ‘ @vcServerName (sysname) - SQL Server to which the query is directed defaulted’

Print ‘ to @@ServerName.’

Print ”

Print ‘Example:’

Print ’sp_SQLSMTPMail ”<user@mycompany.com>”, ”This is a test”, @vcSMTPServer = <network smtp relay server>’

Print ”

Print ‘The above example will send an smpt e-mail to <user@mycompany.com> from @@ServerName’

Print ‘with a subject of ”Message from SQL Server <@@ServerName>” and a’

Print ‘text body of ”This is a test” using the network smtp server specified.’

Print ‘See the MSDN online library, Messaging and Collaboration, at ‘

Print ‘http://www.msdn.microsoft.com/library/ for details about CDOSYS.’

Print ’subheadings: Messaging and Collaboration>Collaboration Data Objects>CDO for Windows 2000>’

Print ‘Reference>Fields>http://schemas.microsoft.com/cdo/configuration/>smtpserver field’

Print ”

Print ‘Be sure to set the default for @vcSMTPServer before compiling this stored procedure.’

Print ”

Return

End

– Declare variables

Declare @iMessageObjId int

Declare @iHr int

Declare @iRtn int

Declare @iFileExists tinyint

Declare @vcCmd varchar(255)

Declare @vcQueryOutPath varchar(50)

Declare @dtDatetime datetime

Declare @vcErrMssg varchar(255)

Declare @vcAttachment varchar(1024)

Declare @iPos int

Declare @vcErrSource varchar(255)

Declare @vcErrDescription varchar(255)

– Set local variables.

Set @dtDatetime = getdate()

Set @iHr = 0

– Check for minimum parameters.

If @vcTo is null

Begin

Set @vcErrMssg = ‘You must supply at least 1 recipient.’

Goto ErrMssg

End

– CDOSYS uses commas to separate recipients. Allow users to use

– either a comma or a semi-colon by replacing semi-colons in the

– To, CCs and BCCs.

Select @vcTo = Replace(@vcTo, ‘;’, ‘,’)

Select @vcCC = Replace(@vcCC, ‘;’, ‘,’)

Select @vcBCC = Replace(@vcBCC, ‘;’, ‘,’)

– Set the default SQL Server to the local SQL Server if one

– is not provided to accommodate instances in SQL 2000.

If @vcServerName is null

Set @vcServerName = @@servername

– Set a default “subject” if one is not provided.

If @vcSubject is null

Set @vcSubject = ‘Message from SQL Server ‘ + @vcServerName

– Set a default “from” if one is not provided.

If @vcFrom is null

Set @vcFrom = ‘SQL-’ + Replace(@vcServerName,‘\’,‘_’)

– Set a default “sender name” if one is not provided.

If @vcSenderName is null

Set @vcSenderName = ‘SQL-’ + Replace(@vcServerName,‘\’,‘_’)

– Create the SMTP message object.

EXEC @iHr = sp_OACreate ‘CDO.Message’, @iMessageObjId OUT

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error creating object CDO.Message.’

Goto ErrMssg

End

– Set SMTP message object parameters.

– To

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘To’, @vcTo

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “To”.’

Goto ErrMssg

End

– Subject

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘Subject’, @vcSubject

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “Subject”.’

Goto ErrMssg

End

– From

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘From’, @vcFrom

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “From”.’

Goto ErrMssg

End

– CC

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘CC’, @vcCC

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “CC”.’

Goto ErrMssg

End

– BCC

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘BCC’, @vcBCC

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “BCC”.’

Goto ErrMssg

End

– DSNOptions

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘DSNOptions’, @vcDSNOptions

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “DSNOptions”.’

Goto ErrMssg

End

– Sender

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘Sender’, @vcSenderName

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “Sender”.’

Goto ErrMssg

End

– Is there a query to run?

If @vcQuery is not null and @vcQuery <> ”

Begin

– We have a query result to include; temporarily send the output to the

– drive with the most free space. Use xp_fixeddrives to determine this.

– If a temp table exists with the following name drop it.

If (Select object_id(‘tempdb.dbo.#fixeddrives’)) > 0

Exec (‘Drop table #fixeddrives’)

– Create a temp table to work with xp_fixeddrives.

Create table #fixeddrives(

Drive char(1) null,

FreeSpace varchar(15) null)

– Get the fixeddrive info.

Insert into #fixeddrives Exec master.dbo.xp_fixeddrives

– Get the drive letter of the drive with the most free space

– Note: The OSQL output file name must be unique for each call within the same session.

– Apparently OSQL does not release its lock on the first file created until the session ends.

– Hence this alleviates a problem with queries from multiple calls in a cursor or other loop.

Select @vcQueryOutPath = Drive + ‘:\TempQueryOut’ +

ltrim(str(datepart(hh,getdate()))) +

ltrim(str(datepart(mi,getdate()))) +

ltrim(str(datepart(ss,getdate()))) +

ltrim(str(datepart(ms,getdate()))) + ‘.txt’

from #fixeddrives

where FreeSpace = (select max(FreeSpace) from #fixeddrives )

– Check for a pattern of ‘\\*\’ or ‘?:\’.

– If found assume the query is a file path.

If Left(@vcQuery, 35) like ‘\\%\%’ or Left(@vcQuery, 5) like ‘_:\%’

Begin

Select @vcCmd = ‘osql /S’ + @vcServerName + ‘ /E /i’ +

convert(varchar(1024),@vcQuery) +

‘ /o’ + @vcQueryOutPath + ‘ -n -w5000 ‘

End

Else

Begin

Select @vcCmd = ‘osql /S’ + @vcServerName + ‘ /E /Q”‘ + @vcQuery +

‘” /o’ + @vcQueryOutPath + ‘ -n -w5000 ‘

End

– Execute the query

Exec master.dbo.xp_cmdshell @vcCmd, no_output

– Add the query results as an attachment if the file was successfully created.

– Check to see if the file exists. Use xp_fileexist to determine this.

– If a temp table exists with the following name drop it.

If (Select object_id(‘tempdb.dbo.#fileexists’)) > 0

Exec (‘Drop table #fileexists’)

– Create a temp table to work with xp_fileexist.

Create table #fileexists(

FileExists tinyint null,

FileIsDirectory tinyint null,

ParentDirectoryExists tinyint null)

– Execute xp_fileexist

Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath

– Now see if we need to add the file as an attachment

If (select FileExists from #fileexists) = 1

Begin

– Set a variable for later use to delete the file.

Select @iFileExists = 1

– Add the file path to the attachment variable.

If @vcAttachments is null

Select @vcAttachments = @vcQueryOutPath

Else

Select @vcAttachments = @vcAttachments + ‘; ‘ + @vcQueryOutPath

End

– Check for multiple attachments separated by a semi-colon ‘;’.

If @vcAttachments is not null

Begin

If right(@vcAttachments,1) <> ‘;’

Select @vcAttachments = @vcAttachments + ‘; ‘

Select @iPos = CharIndex(‘;’, @vcAttachments, 1)

While @iPos > 0

Begin

Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1)))

Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos)

EXEC @iHr = sp_OAMethod @iMessageObjId, ‘AddAttachment’, @iRtn Out, @vcAttachment

IF @iHr <> 0

Begin

EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out

Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) +

char(13) + char(10) + ‘Error adding attachment: ‘ +

char(13) + char(10) + @vcErrSource + char(13) + char(10) +

@vcAttachment

End

Select @iPos = CharIndex(‘;’, @vcAttachments, 1)

End

– TextBody

EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘TextBody’, @vcBody

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message parameter “TextBody”.’

Goto ErrMssg

End

– Other Message parameters for reference

–EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘MimeFormatted’, False

–EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘AutoGenerateTextBody’, False

–EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘MDNRequested’, True

– Set SMTP Message configuration property values.

– Network SMTP Server location

EXEC @iHr = sp_OASetProperty @iMessageObjId,

‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’,

@vcSMTPServer

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message configuraton field “smtpserver”.’

Goto ErrMssg

End

– Sendusing

EXEC @iHr = sp_OASetProperty @iMessageObjId,

‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/sendusing”).Value’,

@cSendUsing

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message configuraton field “sendusing”.’

Goto ErrMssg

End

– SMTPConnectionTimeout

EXEC @iHr = sp_OASetProperty @iMessageObjId,

‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout”).Value’,

@vcTimeout

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message configuraton field “SMTPConnectionTimeout”.’

Goto ErrMssg

End

– SMTPServerPort

EXEC @iHr = sp_OASetProperty @iMessageObjId,

‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPServerPort”).Value’,

@vcPort

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message configuraton field “SMTPServerPort”.’

Goto ErrMssg

End

– SMTPAuthenticate

EXEC @iHr = sp_OASetProperty @iMessageObjId,

‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate”).Value’,

@cAuthenticate

IF @iHr <> 0

Begin

Set @vcErrMssg = ‘Error setting Message configuraton field “SMTPAuthenticate”.’

Goto ErrMssg

End

– Other Message Configuration fields for reference