Solved

SQL Mail Problems

Posted on 2004-10-15
7
918 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Accepted Solution

by:
MartinCMS earned 500 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now