[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Mail Problems

Posted on 2004-10-15
7
Medium Priority
?
949 Views
Last Modified: 2012-05-05
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
Comment
Question by:pbrazil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Accepted Solution

by:
MartinCMS earned 2000 total points
ID: 12319261
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
 
LVL 4

Expert Comment

by:davehilditch
ID: 12319837
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
 

Author Comment

by:pbrazil
ID: 12320802
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:pbrazil
ID: 12320825
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
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12320914
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
 
LVL 4

Expert Comment

by:davehilditch
ID: 12320976
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
 

Author Comment

by:pbrazil
ID: 12321355
Thanks for your help with this guys, the xsmtp_senmail seems like the easiest method for us.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question