Prob with Mail following DTS Pkg Send Mail Fail
-
Get the following error with xp_sendmail or DTS Mail task frequently but inconsistently on a server running Windows 2000 SP2 and SQL Server 2000 SP2. Mail client is Outlook 2000 and has been functioning fine until now.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Additional information is Event ID: 17052 and file name is sqlenv70.rll
Any information on this would be greatly appreciated. Following this error, the only way to clear this is stop all services and restart. Argh!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Has anything changed on the server or the exhchange server recently?
Also, when you say you stop and restart, is this to get SQL Mail working, or is there a problem with SQL Server and/or Agnet?
Steve Jones
-
Yes, this is to get SQL Mail working again and both the Server and the Agent are working fine except with mail. I can still mail from the client directly, no problem there, just with the interface between SQL and the mail client, assuming the MAPI32.dll interface is in question. Reinstalled the client, Outlook but no help. Maybe I should do it again and make sure that I delete that dll prior to reinstallation just in case it is corrupted.
Any other thoughts?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
-
Not yet, will do and let you know how I make out. Thanks Steve!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
-
More notes on this issue;
Once a mail task fails I can't repeat can't kill the process. Once I stop SQLServerAgent, it won't restart, stays in starting mode perpetually. Have to stop MSSQLServer then restart and all works again. This is too weird.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Couple of items to check out:
1 - Do you have sql agent running as an nt user set with admin privileges on your server and the same login used for msdtc and sqlserver? That sounds like part of the permissions problem.
2 - Is it a must in your configuration to have Outlook on the same server as your sql server? From the painful digging that I did, found that the recommendation was to not have them on the same server.
FYI, We finally chose to create a global sp that uses cdonts mail instead.
Hope these are some things that may help you.
Jody
-
Yes on the account. This is a previously working installation that has suddenly decided to fail. Did verify account was working properly and reset all services appropriately.
Not sure how you would configure to use Mail client on another server but, willing to hear about this if you have information.
Started looking at the CDONTS possibilities but have not done enough digging as of yet to see if this will work or how to implement. Oddly enough that was going to be my pet project this week anyway. Hate abandoning a working solution just because a problem arises though. Must fix mentality. Sorry. Any information you have on the CDONTS process is definitely something I would like to see so, either send directly or post. Thanks again.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I got a sample proc from the scripts at this site. You need to have Microsoft SMTP service running on the server.
I also wonder if at the domain level there was a change to the login's policy that allows it to be a service account? Had that issue creep up here.
Have you rebooted the server? I had found an article about Outlook having memory leaks that caused it to not release resources properly and rebooting becomes the option of the day.
Below is what I have put on our servers in the master db so it is available server-wide.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.xsp_Send_CDONTS_Mail_PKG Script Date: 1/23/2002 3:18:46 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xsp_Send_CDONTS_Mail_PKG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xsp_Send_CDONTS_Mail_PKG]
GO
CREATE PROCEDURE xsp_Send_CDONTS_Mail_PKG
@strFrom varchar(255),
@strTo varchar(255),
@strSubject varchar(255),
@strBody varchar(255)
AS
/*****************************************************************************
xsp_Send_CDONTS_Mail_PKG
Description: Sends mail through SMTP as CDO mail.
Owned By: $DBA
Project: General Function in lieu of SQL Mail
OCX:
Method:
Parameters: @strFrom - Email from address
@strTo - Email to address
@strSubject - Email Subject
@strBody - Email message body
Status Returns:[See Other Outputs: ]
Other Outputs:
Example: Exec xsp_Send_CDONTS_Mail_PKG "goughj@fcsamerica.com", "$DBA@fcsamerica.com", "My Email", "Email Message"
-------------------------------------------------------------------------------------------------------
History: [Name][Date]
Created :Jody Gough 11/13/2001
Unit Tested :
User Sign-Off :
Production :
--------------------------------------------------------------------------------------------
Modified : [Name][Date]
Description :[In this block include the description of the modificationand
the Tested, Production Who and When blocks as above.]
Unit Tested :
User Sign-Off :[Name][Date]
Production :[Name][date]
*****************************************************************************/
DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
EXEC @hr = sp_OASetProperty @object, 'From',@strFrom
EXEC @hr = sp_OASetProperty @object, 'Body', @strBody
EXEC @hr = sp_OASetProperty @object, 'Subject', @strSubject
EXEC @hr = sp_OASetProperty @object, 'To', @strTo
EXEC @hr = sp_OAMethod @object, 'Send', NULL
EXEC @hr = sp_OADestroy @object
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Jody
-
Jody - I probably should have made this clear before and looking back at the posts, it doesn't appear that I have. Each time I restart the services, I am able to do some sendmail tasks fine but then..... it fails and once it fails, it leaves the connection open, the Mapi32.dll open and won't close the SQL connection until I stop and start the MSSQLServer service. Once again, not sure where to go.
Fortunately this is a low user connection "datawarehouse" server. Unfortunately it is my server with the most DTS packages for data loads and each package has mail tasks in them so that the customer knows their data load process is complete every day. Aren't I nice
?David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Good points there sherlock! I would not want to delve into changing load processes!
I wonder if there is an email account that has changed or one of the packages that has ever so slight a 'variance' that may be causing your failures. I know I am the perfect typist! (NOT)
Unless you have made recent system changes, like service pack updates, maybe look at the settings on the most recent dataload processes. (or...do you have any 'helpers'?)
Do you have any mts interfaces on this system?
-
No helpers, no changes, and no on the MTS usage.
The only thing that is even a remote possibility is a network outage over the weekend affecting that server but, all is back up and working, no other difficulties and as in Steve's post all other functions are working well except mail.
Hoping the Outlook update that Steve suggested will fix problem.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Any changes to DTS packages that might attach files? Perhaps some file attachment is causing the failure. There is an error in the KB mentioning sending open files.
Steve Jones
-
I attach the results of a sum query for customer review. What is the article num?
Not sure that I will need it because after running the Office SR1 update this appears to be working well. Approximately 100 sends from both T-SQL xp_sendmail and DTS package execution manual and as a job. Nice call Steve!
Odd but I was hoping for something more dynamic for a solution. Not that I am complaining. Odd too that this just "popped" up?!..?
I'll keep you all posted after tonights run with the scheduled jobs.
Thanks again Steve and Jody.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply