SQL Mail

[marcial4]

Hello everybody, I have a problem right now with Mail feature of SQL Server 2000. I have a DTS processed by a Job who send a mail when some specific process fail to do, but when it's the moment to send the mail, the job fail and the log file says the following:

Step 'DTSStep_DTSActiveScriptTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft OLE DB Provider for SQL Server
Error Description: xp_sendmail: failed with mail error 0x80040115

Error on Line 100
(Microsoft OLE DB Provider for SQL Server (80040e14): xp_sendmail: failed with mail error 0x80040115)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

I make a test in SQL Enterprise Manager inside the DTS and execute the step sending the mail successfully. What's happening here??? All SQL services run with the same user, In the server exists Microsoft Outlook 2000. And everything works perfectly before... Any help will be appreciated. Thank you very much.

Marcial Quintero
Maracaibo, Venezuela

 

[tlbroadbent]

0x80040115 is a MAPI NETWORK ERROR which could have many causes. Inadequate permissions could cause this error.

Do any process run that stop SQLMail (i.e., execute xp_stopmail)? If so, you may want to eliminate those processes so SQLMail runs all the time when SQL Server is running.

This error occurs on our servers from occasionally. When xp_sendmail is executed it must connect to the Exchange Server. Sometimes xp_sendmail times out before the connection is made. Executing xp_sendmail the again usually works.

We scheduled a JOB to run daily at about 4:00AM to send mail from each server to the DBAs This usually insures that Mail will work for the remainder of the day. If the JOB fails we also get a message and can restart the JOB to make sure SQLMail is working. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[marcial4]

Thank you for answer me but when you said "inadecuate permission" are you meaning about user's permissions on Windows 2000 Server, or User permissions on the jobs, the DTS or users in general on SQL Server 2000?

 

[tlbroadbent]

I refer to inadequate network permissions. Is the SQL login account a domain user? Was the Exchange profile setup using the SQL login account? Are you using the DTS Send Mail Task or Execute SQL Task? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[marcial4]

Ok Terry, the user used to log the SQL service and the SQL Server agent service is a domain user and Microsoft Outlook 2000 was configured with the mail account of the user I used to log with SQL.

The error I posted the first time was in a DTS who execute a SQL Task on a Job.

When I use a different DTS with the XP_Sendmail procedure and running it with a job the error is:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSSendMailTask_1 DTSRun OnError: DTSStep_DTSSendMailTask_1, Error = -2147220352 (80040480) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220352 (80040480); Provider Error: 0 (0) Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 DTSRun OnFinish: DTSStep_DTSSendMailTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

The strange thing is the XP_sendmail procedure work perfectly when you running it directly without use a Job.

thanks for everything...

Marcial.