SQL Server Performance Forum – Threads Archive

Mail Program – XP_SendMail Alternatives

Is there any other email program that could be used to send emails from sql server. Currently using xp_sendmail and this uses MAPI and some times causes errors and email alerts are not sent out. Need to install outlook on the sql server creating a profile that sends emails.
Is there a program that can eliminate MAPI and outlook installation that can send emails via sql server

Here is an sp I found, but have not yet tested myself on an another forum by David Scotland, basically it uses collaboration data objects. CDO is installed by default on 2000 & 2003 server as long as you don’t need to process received mail it should work fine, plus you dont need outlook on the server. CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) = "[email protected]",
@To varchar(100) ,
@CC varchar(300)= " ",
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@Attachment varchar(255) = " " /********************************************************************* This stored procedure takes the parameters and sends an e-mail.
Comments are added to the stored procedure where necessary. ***********************************************************************/
AS
Declare @iMsg int
Declare @iBp int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000) –************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate ‘CDO.Message’, @iMsg OUT –***************Configuring the Message Object ******************
— This is to configure a remote SMTP server.
—http://msdn.microsoft.com/library/d…ml/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value’,’2′
— This is to configure the Server Name or IP address.
— Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value’, ‘post.demon.co.uk’ — Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, ‘Configuration.Fields.Update’, null — Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, ‘To’, @To
EXEC @hr = sp_OASetProperty @iMsg, ‘CC’, @CC
EXEC @hr = sp_OASetProperty @iMsg, ‘From’, @From
EXEC @hr = sp_OASetProperty @iMsg, ‘Subject’, @Subject EXEC @hr = sp_OASetProperty @iMsg, ‘TextBody’, @Body
EXEC @hr = sp_OAMethod @iMsg, ‘AddAttachment’, NULL, @Attachment
EXEC @hr = sp_OAMethod @iMsg, ‘Send’, NULL — Error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ‘ Source: ‘ + @source
PRINT @output
SELECT @output = ‘ Description: ‘ + @description
PRINT @output
END
ELSE
BEGIN
PRINT ‘ sp_OAGetErrorInfo failed.’
RETURN
END
END — Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

We use a proc very similar to the above (although I modified it to also work for NT) it works well for sending emails, although you can’t use SQL’s email alerting process Cheers
Twan

http://www.sqlteam.com/item.asp?ItemID=401 – to get to know more about SQLMail.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315886 – common SQL Mail problems and resolve them.
http://support.microsoft.com/view/tn.asp?kb=312839 – sending email without using SQL Mail.
http://www.sqlteam.com/item.asp?ItemID=5003 – Sending smtp email from a SP. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I would recommend the extended stored procedure called XPSMTP:
http://www.sqldev.net/xp/xpsmtp.htm

True, another useful tool and reference by Argyle.
(I couldn’t find this link on my library) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Argly, Satya… can we able to send query results (daily report as job) using XPSMTP? cos I have read the doc available on the site sqldev.net and it isn’t mentioned there. if not could you please suggest alternative method without using SQL Mail. Thanks in Advance……

]]>