Mail Comparison

Question

Mail Comparison

Bidec

SSCommitted

Points: 1666
More actions
April 21, 2004 at 8:51 am

#85051

Hi All,
Till now we had outlook client installed on our SQL server and we were getting email notifications if something failed ... and all was fine. But now we are getting ready to move to new SQL server boxes and somebody threw up a idea of using SMTP mail.
I have gone thru some Q articles and read about how to configure SMTP mail on SQL server...
Has anyone worked on both SQL mail ( with outlook client on server) and SMTP mail ? which one is better ? I am just looking for a comparison chart here....
Thanks... any help is appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Go with SQLMail, it's better, nuff said.

Seriously though, configuration is simple with both, however SQL Mail give you options like @query that you just don't get with smtp mail. The simplest implementation of smtp mail I have had was to use xp_smtpmail from sqlteam.com, I am using it now as I don't have access to an exchange server from my SQL domain. It works fine, but I miss the functionality in SQL Mail.

Shamless self promotion - read my blog http://sirsql.net

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 2

There are pro and con for using sql-mail. If sql-mail really die, you have to stop and re-start sql server. I have never heard of smtp mail problem in which you have to reboot sql server or the server itself.

I download smtp mail from this forum a year or more ago and it did include @query parameter. I could repost it back if needed. It seems that the library no longer has this particular script for some reason.

mom

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

If you have a version with @query that would be awesome, it'd save me a lot of work regarding dumping queries to text files and then having to attach those to emails.

Shamless self promotion - read my blog http://sirsql.net

Bidec SSCommitted Points: 1666 More actions · Answer 4

Ya... even I would like to have a look at the new script with @query parameter.

"Mom" do you see any other pros with SMTP mail ?

Thanks.

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 5

ranec, I think no having to reboot sql or the server itself is the most benefit. At my shop we are still using sql-mail, but with sql 2005, we have to use smtp mail because sql-mail will no longer be support... well that's what I heard, I have not test sql 2005 yet because my group think it's a waist of time to look at a product that it is not even out yet.

One other good reason is the fact that you could use smtp mail if your server is outside the firewall and could not connect to exchange server.

mom

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 6

if exists (select *

from sysobjects

where id = object_id(N'[dbo].[sp_SQLSMTPMail]')

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_SQLSMTPMail]

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

Create Procedure dbo.sp_SQLSMTPMail

@vcTo varchar(2048) = null,

@vcBody varchar(8000) = '',

@vcSubject varchar(255) = null,

@vcAttachments varchar(1024) = null,

@vcQuery varchar(8000) = null,

@vcFrom varchar(128) = null,

@vcCC varchar(2048) = '',

@vcBCC varchar(2048) = '',

@vcSMTPServer varchar(255) = 'mail.yourcompan.com', -- put local network smtp server name 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

As

/*******************************************************************/

--Name : sp_SQLSMTPMail

--Server : Generic

--Description : SQL smtp e-mail using CDOSYS, OLE Automation and a

-- network smtp server; For SQL Servers running on

-- windows 2000.

--

--Note : Be sure to set the default for @vcSMTPServer above to

-- the company network smtp server or you will have to

-- pass it in each time.

--

--Comments : Getting the network SMTP configured to work properly

-- may require engaging your company network or

-- server people who deal with the netowrk SMTP server.

-- Some errors that the stored proc returns relate to

-- incorrect permissions for the various SQL Servers to

-- use the SMTP relay server to bouce out going mail.

-- Without proper permissions the SQL server appears as

-- a spammer to the local SMTP network server.

--

--Parameters : See the 'Syntax' Print statements below or call the

-- sp with '?' as the first input.

--

--Date : 08/22/2001

--Author : Clinton Herring

--

--History :

/*******************************************************************/

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("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver&quot.Value'">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("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing&quot.Value'">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("<A href="http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout&quot.Value'">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("<A href="http://schemas.microsoft.com/cdo/configuration/SMTPServerPort&quot.Value'">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("<A href="http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate&quot.Value'">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