December 12, 2006 at 4:12 pm
Hi... We have been using sp_SQLSMTPMail as found on this site to be able to send emails from all of our SQL Servers. This has been working since implementation years ago until about 2 weeks ago. For what ever reason a small number of our servers have since stopped working (about a dozen died the same day). We will have dozens of servers that continued working without any type of problem at all.
The error we are getting through the SP is:
Error setting Message configuraton field "smtpserver".
CDO.Configuration.1
A dynamic link library (DLL) initialization routine failed.
We do not know what is causing this to happen but have found that stopping and restarting SQL fixes it. Has anyone else experienced this and know of a fix for it? Any help would be greatly appreciated.
Thanks,
Chad
December 12, 2006 at 5:43 pm
You can use SQL Server SMTP Mail XP from sqldev.net...
http://www.sqldev.net/xp/xpsmtp.htm
MohammedU
Microsoft SQL Server MVP
December 13, 2006 at 11:28 am
Not quite what I was looking for....
We need to continue to support SQL queries as attachments which sp_SQLSMTPMail provides where the SMTP Mail XP does not.
I am hoping that someone might know why this one up and dies on us and what we can do to resolve it. It is strange that it has worked fine for years and then in one day stopped working on a handful of our servers.
Is there another email process out there that does support queries/attachments?
Thoughts?
December 14, 2006 at 2:46 pm
BTW... I did go ahead and try XPSMTP from SQLDEV and it appears to be suffering from the same issue for me. The same servers that are affected for sp_SQLSMTPMail are also affected for XPSMTP. The servers that work with sp_SQLSMTPMail also work for XPSMTP.
The error I get from XPSMTP is:
Msg 0, Level 16, State 0, Procedure xp_smtp_sendmail, Line 2
Cannot load the DLL xpsmtp80.dll, or one of the DLLs it references. Reason: 1114(A dynamic link library (DLL) initialization routine failed.).
The error I get from sp_SQLSMTPMail is:
Error setting Message configuraton field "smtpserver".
CDO.Configuration.1
A dynamic link library (DLL) initialization routine failed.
Also, this is on a per instance basis. We have a server that has 4 instances of SQL. 3 of them do not work currently and 1 of them does. If I stop and restart SQL on the affected instance then that 1 instance will begin to work again.
Any thoughts?
August 7, 2007 at 2:34 pm
Anyone ever solve this? I am encountering the same error in our environment.
Windows Server 2003 SP1
SQL Server 2005 SP1
This is the only server that is NOT working with stored procedure. Other servers run the SP fine with this OS/SQL Server installation.
I ran the following VBScript and this can send e-mail fine from the server, so I believe there is some issue with the stored procedure
'==========================================================================
'
' VBScript Source File -- Created with SAPIEN Technologies PrimalScript 4.1
'
' COMMENT: This script is just the basic code necessary to send e-mail in script.
' Use "Output" to gather data to write to the mail.
'
'==========================================================================
MailSubject = "Basic Mail Message"
Output = MailSubject & ":" & VbCrLf
j = 1
' This is the distribution list the report will be sent to (If 'Mail' set to 1)
' Example: SendTo = "admingroup@mydomain.com"
SendTo = "<e-mail>"
' This is the SMTP server's IP Address (If you requested a report by mail)
SMTPIP = <IP_Address OR Servername>
' Example: SMPTPIP = "172.98.64.111"
Output = Output & VbCrLf & "(" & j & ") " & Now & " - Art's wishes he was that cool"
j = j + 1
Call SendMail
'==========================================================================
'Sub routine to Send Mail
Sub SendMail
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "<Email@email.com>"
objemail.To = SendTo
objEmail.Subject = MailSubject
objEmail.Textbody = vbCrLf & Output
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPIP
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Set objEmail = Nothing
End Sub
Any way to trace this running on the SQL Server as Profiler is no help. Is there some error trapping that can be added to identify why the stored procedure does not work and the VB Script does?
August 7, 2007 at 2:41 pm
We did not ever solve it (even with Microsoft looking into it). We did implement a workaround though by using a LinkedServer from the affected servers. We then changed that local copy of the SP to simply call the SP on another server that was working.
The only other option we really had was to restart SQL now and then to get it working again. That was not an option we wanted to go with.
While the workaround we implemented might work for you, it sounds like it might be a different issue. We only ever saw this issue on Window Server 2000 boxes. Good luck with it though!
Chad
August 10, 2007 at 12:51 pm
Rebooting our server resolved the issue.
October 9, 2007 at 1:36 pm
Is any one found answer on this problem? I don't like rebooting the server every two weeks.
October 9, 2007 at 2:05 pm
We never did find out why this happened. Our solution, as stated above, was to use a link server for sending out notifications. The local sp_SQLSMTPMail sp was modified to call the same stored procedure on a different machine by using link servers. That work around has worked successfully for us since then.
Chad
October 9, 2007 at 10:40 pm
Sounds like a DNS problem to me. Has anyone tried an "ipconfig /flushdns" on the affected servers? Your infrastructure folks may have changed the IP address or DNS entry for your SMTP server?
Joe
October 10, 2007 at 7:31 am
The issue first addressed with this was not a DNS issue. It specifically had to do with cdo and SQL on a Windows 2000 server.
We worked with Microsoft and could send email through the same SMTP server from the O/S (outside of SQL). Also, on multiple instance machines we had some instances work and others not. Restarting the affected instance was enough to get it going again. But after a few weeks it would once again be broken.
We never did come to a root cause for it. I believe this issue was caused by Microsoft patching the month before we experienced this issue. But that is my guess only.
Our only solution that worked was the work-around described above.
Chad
October 10, 2007 at 7:48 am
I am using SQL 2005 and did work perfectly for a 3 months. my guess is the same like yours that some updates cause that.
January 22, 2008 at 2:11 pm
Chad Carter (10/9/2007)
We never did find out why this happened. Our solution, as stated above, was to use a link server for sending out notifications. The local sp_SQLSMTPMail sp was modified to call the same stored procedure on a different machine by using link servers. That work around has worked successfully for us since then.Chad
Chad,
I was able to do this as well, but I cannot get attachments working. Can you tell me how you did it?
Thanks!
David Hay
david hay
January 22, 2008 at 9:50 pm
I needed to change this code to make it a use a UNC path instead of just using the drive letter.
Select @vcQueryOutPath = Drive + ':\TempQueryOut' +
ltrim(str(datepart(hh,getdate()))) +
ltrim(str(datepart(mi,getdate()))) +
ltrim(str(datepart(ss,getdate()))) +
ltrim(str(datepart(ms,getdate()))) + '.txt'
from #fixeddrives
where FreeSpace = (select max(FreeSpace) from #fixeddrives )
Unfortunately, I don't have the original code anymore since I switched companies. I believe is was something like this though:
Select @vcQueryOutPath = '\\' +
CONVERT(varchar(50),SERVERPROPERTY('MachineName')) +
'\' + Drive + '$\TempQueryOut' +
ltrim(str(datepart(hh,getdate()))) +
ltrim(str(datepart(mi,getdate()))) +
ltrim(str(datepart(ss,getdate()))) +
ltrim(str(datepart(ms,getdate()))) +
'.txt'
from #fixeddrives
where FreeSpace = (select max(FreeSpace) from #fixeddrives )
Go ahead and work with it and I am sure you will get it. We simply used the administrative share to the drive since our account had access to it. You may need to do something different.
Oh, and after you call the SP on the other server, make sure you still go through the cleanup routine in the calling SP to cleanup the file that was created.
Good luck,
Chad
August 12, 2008 at 7:47 am
Here both built-in Database Mail service and our email SP stopped working 2 weeks ago for no apparent reason. I've traced back the problem to this fix :
http://support.microsoft.com/kb/941105/en-us
Still don't know if I want to take the risk of installing the hotfix or use the workaround.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply