SQL Mail Problems

Hi All,

We are having problems with SQL Mail.  We depend heavily on xp_sendmail to send out reports, and our exchnage server has been going down frequently.  

When SQL Generates an e-mail when the exchange server is down, we get the following error message:

xp_sendmail: failed with mail error 0x80040115 [SQLSTATE 42000] (Error 18025).  The step failed.

Why wouldn't this message be stored in the Outlook outbox and get sent when the connection to the exchange server was back up ?

Also, would configuring SQL mail to use an smtp profile be of any help with the queing issue.  Would this methodology provide the ability of the message to be queued and sent when the mail server became available.

Which Methodology would be the best fro our situation.

Any help on this would be greatly appreciated, we are going nuts re-generating mail messages !!

Who is Participating?
MartinCMSConnect With a Mentor Commented:
per half you might want to consider using XPSMTP.dll - SQL Server SMTP Mail XP.

It much more reliable then using xp_sendmail.  Here is the following link for info on XPSMTP

We use the following code which uses cdosys - this will provide the kind of behaviour your requesting - it just drops it on an outgoing queue and returns to the stored procedure immediately.  Whoever is set as the sender of the email, will receive an email if it's not deliverable.

You might need to add authentication, depending on your  smtp server, but the configuration options are available at the link provided in the comments in the code.

Dave Hilditch

CREATE  PROCEDURE [dbo].[sp_send_cdosysmail]
   @From         varchar(100) ,
   @To           varchar(100) ,
   @Subject      varchar(100)=" ",
   @Body         varchar(4000) =" " ,
   @IP_Address   varchar(100)=''

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:

   Declare @iMsg 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/default.asp?url=/library/en-us/cdosys/html/_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', @IP_Address

-- 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, 'From', @From
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
   IF @hr <>0
     select @hr
       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
       IF @hr = 0
           SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
           PRINT '  sp_OAGetErrorInfo failed.'

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg

pbrazilAuthor Commented:
Thanks for you replies, DOes eithe rsolution provide the ability to attach a query to have th eresilts e-mail the way xp_sendmail odes.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

pbrazilAuthor Commented:
Also, why isn't arent the xp_sendmail messages being stored in the outbox in Outlook.

Do we have something confifured wrong in outlook or SQl mail
since we don't know how you're using SQL mail with outlook, really can't tell you much about that.  XPSMTP.dll do have the file attach capability just like xp_sendmail.  In fact, have lots more available function then xp_sendmail.
some of the unique function xp_sendmail doesn't have:
1) you can specify the Reply to email address defferently then the sender.
2) you can specify the Priority of the message

check out the Parameters in http://sqldev.net/xp/xpsmtp.htm
Your DB is the most valuable part of your system - don't install Outlook on the server that hosts your DB.  It has been proven in the past to contain many many security holes that can allow hackers access to the computer.  Service packs fix this and security releases fix this, but only until the next flaw is discovered.

cdosys is totally comprehensive - it's the standard api for handling email.  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

It will handle any kind of attachments you want to throw at it, but you'll need to modify the stored procedure I posted up to attach files. Check out the link above for full info on cdosys and how to add attachments.

Dave Hilditch
pbrazilAuthor Commented:
Thanks for your help with this guys, the xsmtp_senmail seems like the easiest method for us.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.