Solved

SQL Mail Problems

Posted on 2004-10-15
7
926 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 43
Run SQL Server Proc from Access 11 31
MS SQL + Insert Into Table - If Doesnt Exist 9 35
SSRS Enable Remote Errors 4 26
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

773 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