MS SQL Server - My Solution for SQL Mail on SBS

Author

My Solution for SQL Mail on SBS

Nicolas Verhaeghe

2005-08-30, 8:23 pm

It seems that most of the SBS admins have had the same problem with SQL Mail
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.

I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.

Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.

-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@To varchar(8000) = null,
@Subject varchar(255) = null,
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null
)
AS

-- Declare
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message
object
EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the
configuration object

-- Configuration Object
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsing
Method)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServe
r)', 'localhost' --
SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServe
rPort)', 25 --
Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthe
nticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate

EXEC sp_OAMethod @config, 'Fields.Update'

-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set
message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject

IF (@From Is Not Null) AND (@From Like '%@%')
BEGIN
EXEC @hr = sp_OASetProperty @message, 'From', @From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @hr = sp_OASetProperty @message, 'From', 'defaultsend@email.com'
END

IF (@Cc Is Not Null) AND (@Cc Like '%@%')
BEGIN
EXEC @hr = sp_OASetProperty @message, 'CC', @Cc
END

IF (@Bcc Is Not Null) AND (@Bcc Like '%@%')
BEGIN
EXEC @hr = sp_OASetProperty @message, 'BCC', @Bcc
END

IF (@HTMLFormat = 1)
BEGIN
EXEC @hr = sp_OASetProperty @message, 'HTMLBody', @Body
END
ELSE BEGIN
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
END

-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@Attachments Is Not Null) AND (@Attachments <> '')
BEGIN
EXEC @hr = sp_OASetProperty @message, 'AddAttachment', @Attachments
END

EXEC sp_OAMethod @message, 'Send()'

-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config

-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary
(4),@hr), Source=@src, Description=@desc
RETURN
END

GO

/* Test below - Replace @email addresses with real addresses to test

EXEC dbo.sp_sendSMTPmail
@To='recipient@email
.com',
@Subject='This is a test',
@Body='HTML Body',
@Cc='',
@Bcc='',
@Attachments='',
@HTMLFormat=1,
@From='sender@email.com'

*/

Geoff N. Hiten

2005-08-30, 8:23 pm

Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"Nicolas Verhaeghe" <nospam_nicver@yahoo.com_nospam> wrote in message
news:4314abac$0$3220
7$39cecf19@news.twtelecom.net...
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
>
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
>
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
>
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @To varchar(8000) = null,
> @Subject varchar(255) = null,
> @Body text = null,
> @Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @Cc varchar(8000) = null,
> @Bcc varchar(8000) = null,
> @Attachments varchar(8000) = null, -- delimeter is ;
> @HTMLFormat int = 0,
> @From varchar(255) = null
> )
> AS
>
> -- Declare
> DECLARE @message int
> DECLARE @config int
> DECLARE @hr int
> DECLARE @src varchar(255), @desc varchar(255)
>
> EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message
> object
> EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the
> configuration object
>
> -- Configuration Object
> EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsing
Method)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServe
r)',
> 'localhost' -- SMTP Server
> EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServe
rPort)', 25 --
> Server SMTP Port
> EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthe
nticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
>
> EXEC sp_OAMethod @config, 'Fields.Update'
>
>
> -- Message Object
> EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set
> message.configuration = config
> EXEC @hr = sp_OASetProperty @message, 'To', @To
> EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
>
>
> IF (@From Is Not Null) AND (@From Like '%@%')
> BEGIN
> EXEC @hr = sp_OASetProperty @message, 'From', @From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @hr = sp_OASetProperty @message, 'From', 'defaultsend@email.com'
> END
>
> IF (@Cc Is Not Null) AND (@Cc Like '%@%')
> BEGIN
> EXEC @hr = sp_OASetProperty @message, 'CC', @Cc
> END
>
> IF (@Bcc Is Not Null) AND (@Bcc Like '%@%')
> BEGIN
> EXEC @hr = sp_OASetProperty @message, 'BCC', @Bcc
> END
>
> IF (@HTMLFormat = 1)
> BEGIN
> EXEC @hr = sp_OASetProperty @message, 'HTMLBody', @Body
> END
> ELSE BEGIN
> EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
> END
>
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@Attachments Is Not Null) AND (@Attachments <> '')
> BEGIN
> EXEC @hr = sp_OASetProperty @message, 'AddAttachment', @Attachments
> END
>
>
> EXEC sp_OAMethod @message, 'Send()'
>
> -- Destroys the objects
> EXEC @hr = sp_OADestroy @message
> EXEC @hr = sp_OADestroy @config
>
> -- Errorhandler
> IF @hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
> SELECT hr=convert(varbinary
(4),@hr), Source=@src, Description=@desc
> RETURN
> END
>
> GO
>
>
> /* Test below - Replace @email addresses with real addresses to test
>
> EXEC dbo.sp_sendSMTPmail
> @To='recipient@email
.com',
> @Subject='This is a test',
> @Body='HTML Body',
> @Cc='',
> @Bcc='',
> @Attachments='',
> @HTMLFormat=1,
> @From='sender@email.com'
>
> */
>

Nicolas Verhaeghe

2005-08-30, 8:23 pm

It uses xp_smtp_sendmail wich is not available without a MAPI profile.

Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.

Geoff N. Hiten

2005-08-30, 8:23 pm

You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"Nicolas Verhaeghe" <nospam_nicver@yahoo.com_nospam> wrote in message
news:4314bda0$0$3220
3$39cecf19@news.twtelecom.net...
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
>
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>

Nicolas Verhaeghe

2005-08-30, 8:23 pm

My solution is specifically when xp_smtp_sendmail does not work.

Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.

If I used your solution it would fail, because xp_smtp_sendmail does not
work.

Steve Foster [SBS MVP]

2005-08-30, 8:23 pm

Nicolas Verhaeghe wrote:

>My solution is specifically when xp_smtp_sendmail does not work.
>
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.

You are confusing xp_sendmail, and xp_smtp_sendmail, I think.

xp_sendmail relies on SQL Mail.

xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Nicolas Verhaeghe

2005-08-30, 8:23 pm

Then I am confusing and I apologize. I guess two solutions are better than
one.

Sponsored Links