• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 965
  • Last Modified:

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 !!

0
pbrazil
Asked:
pbrazil
  • 3
  • 2
  • 2
1 Solution
 
MartinCMSCommented:
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

http://sqldev.net/xp/xpsmtp.htm
0
 
davehilditchCommented:
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
www.matiogi.com


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

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:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
   AS
   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
     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

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

0
 
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
0
 
MartinCMSCommented:
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
0
 
davehilditchCommented:
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
www.matiogi.com
0
 
pbrazilAuthor Commented:
Thanks for your help with this guys, the xsmtp_senmail seems like the easiest method for us.
0
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now